The expression works for me. Can you check to see if all the names have a surname and Prefix. This might happen in case some name is in incorrect format. Try redirecting the error records and use a dataviewer to see it is faining for which names. My Blog | Ask Me

Yes. I Redirected Bad Data to a file. I have used this Error Redirection long back, but forgot that. Thank You once again.

My Requirement is to Split a Column Into 3 Columns My Source Table will have a column called Name. It will be having value as "Mr. ABC XYZ". I need to split it into three columns called FirstName,SurName and Suffix so that values in these columns are as below. FirstName = ABC Surname = XYZ Suffix = Mr. I have used follwing expressions for splitting in SSIS Derived Column FirstNAme = TRIM(SUBSTRING(NAME,(FINDSTRING(NAME," ",1) + 1),(FINDSTRING(NAME," ",2)) - (FINDSTRING(NAME," ",1)))) SurName = TRIM(SUBSTRING(NAME,(FINDSTRING(NAME," ",2) + 1),LEN(NAME) - (FINDSTRING(NAME," ",2)))) and Suffix = TRIM(SUBSTRING(NAME,1,(FINDSTRING(NAME," ",1) - 1))) I have Inserted Value as Mr. Rajesh Hegde in the source column and executed the package. I got the following Error. [CLEAN_UP_TASK [602]] Error: An error occurred while evaluating the function. [CLEAN_UP_TASK [602]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "CLEAN_UP_TASK" (602)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "First_Name" (646)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "CLEAN_UP_TASK" (602) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited. Please help me to know what is the Mistake... Thanks In advance