When importing a datasource from a flat file today (a csv), (which ran through a Derived Column Transformation and then a Data Conversion Transformation before hitting SQL), I hit the following error in SQL Server Integration Services 2008 (SSIS 2008):
"Data conversion failed while converting column .....The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."
The dataviewers and error output SSIS debugging techniques didn't show anything unusual in the data as it was transformed. In addition, this problem only occurred on my float columns, and only when the data in the columns is blank. I did some quick checks to see if there were a few extra spaces in some of the fields and did a TRIM() for the derived column - but the same error kept coming up. I even enabled "Ignore truncation" and it still didn't work.
Information: 0x402090DE at ImportTemplateData, Flat File Source : The total number of data rows processed for file "C:\WorkforceProfileInitialImport\Q2_2008_162_Data.csv" is 1050.
Error: 0xC02020C5 at ImportTemplateData, Data Conversion : Data conversion failed while converting column "[8c Override Census Period FTE]" (2050) to column "Copy of [8c Override Census Period FTE]" (5338). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at ImportTemplateData, Data Conversion : SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Copy of [8c Override Census Period FTE]" (5338)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of [8c Override Census Period FTE]" (5338)" 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: 0xC0047022 at ImportTemplateData, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (4034) failed with error code 0xC0209029 while processing input "Data Conversion Input" (4035). 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.
Information: 0x40043008 at ImportTemplateData, SSIS.Pipeline: Post Execute phase is beginning.
The underlying issue was that it was trying to convert these blank strings (from the flat file source datatype) into the float datatype and was thereforce constantly failing. The fix is to tick on the "Retain null values from the source as null values in the data flow" and the package then started to run successfully for those columns which had just blank values.