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:
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.....
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
Thank u so much for ur solution :-)
Thanks Pal
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
Relay THANK YOU!!!!!!!
Realy THANK YOU!!!!!!!
Thank You David for taking the Effort to write this article. You just save me from one month of pain.
Amazing suggestion mate! You are a genius.
This worked. Thanks :)
Oh, man, this was driving me buggy. Thanks!
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!
Hi Guys! where can I find this window? i dont have much experience in sql and the same error is displayed.. Thank you...
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
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
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.
Thanks for the post, very help full.
Post a Comment