Tuesday, 21 July 2009

Fix SSIS 2008 Error - The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

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 [2917]: The total number of data rows processed for file "C:\WorkforceProfileInitialImport\Q2_2008_162_Data.csv" is 1050.
Error: 0xC02020C5 at ImportTemplateData, Data Conversion [4034]: 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 [4034]: 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.

17 comments:

Anonymous said...

THANK YOU THANK YOU THANK. I have been searching the web looking for a solution. After seeing lots of bad advice/suggestions, I finally found yours.....

JD Long said...

I was pulling my hair out on this today and your blog post came up in a Google search for [error code 0xC020907F status value 2]

I'm so glad you took the time to write this up. Thank you.

-JD Long
Chicago IL

Samyuktha said...

Thank u so much for ur solution :-)

Ugur Naciterhan said...

Thanks Pal

Unknown said...

cool, thanks Dave for the hint.
I run in the same issue and also found this post pretty interesting...

http://ssisblog.replicationanswers.com/2007/11/24/retain-null-values-vs-keep-nulls--which-to-use.aspx

qaiscs said...

Relay THANK YOU!!!!!!!

qaiscs said...

Realy THANK YOU!!!!!!!

Unknown said...

Thank You David for taking the Effort to write this article. You just save me from one month of pain.

Unknown said...

Amazing suggestion mate! You are a genius.

Krithika said...

This worked. Thanks :)

Patman said...

Oh, man, this was driving me buggy. Thanks!

deej said...

You sir have made my day. I have been googling for this a long time and it is such a simple solution :-).

Thank you! Life saver!

Unknown said...

Hi Guys! where can I find this window? i dont have much experience in sql and the same error is displayed.. Thank you...

cs said...

Thanks for the info.
I am new to SSIS. Inspite of checking the check box I still get the following error

[Data Conversion [617]] Error: Data conversion failed while converting column "pre_use_amount" (32) to column "pre_use_amount" (786). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

I am not sure if it is happening after processing the last row. If so how do I avoid it. In dataviewer everything look ok

SUresh

cs said...

Thanks for the info.
I am new to SSIS. Inspite of checking the check box I still get the following error

[Data Conversion [617]] Error: Data conversion failed while converting column "pre_use_amount" (32) to column "pre_use_amount" (786). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

I am not sure if it is happening after processing the last row. If so how do I avoid it. In dataviewer everything look ok

SUresh

Ceci said...

Including the tip above, I also made changes on the Error Output above where I selected 'ignore failure' on the column that contains NULL. The particular column I have is date datatyp and can have NULLs.

Harsh Vardhan Parashar said...

Thanks for the post, very help full.