Derived Column Error in SSIS
Joanolo pointed out if the name had a comma which was not in my case. Therefore, I have changed the format of the derived columns expression to the ContactName as follows:

SupplierFirstName 

SUBSTRING(ContactName,1,FINDSTRING(ContactName," ",1) - 1)      


SupplierLastName

SUBSTRING(ContactName,FINDSTRING(ContactName," ",1) + 1,LEN(ContactName) - FINDSTRING(ContactName," ",1))