Sql-server – SSIS: data types just don’t get converted from string

sql serverssis

That's my first experience in SSIS and I'm just going nuts: NOTHING WORKS.
(Don't be afraid of big post: most is just errors output.)

I've got two MS SQL DBs with same fields and I have to transfer from first one, where everything is in nvarchar(32) aka DT_WSTR, into second one, where types are different.

Data and its examples:
"Timestamp" is "datetime2(7)" in destination, and source looks like ISO 8601: 2013-12-19T00:00:00.000
"Value" is "real" numbers with scientific notation, test examples are: 17e+10, 17.14, 17.14e+5, 1715E+4, 1714
And four columns with just different ints (bigint, bigint, tinyint, int).

Now for what I've tried (warning for lots of quotations):

  • Derived Column. I used casts like "(DT_DBTIMESTAMP2,7)time" and "(DT_R4)value". Perhaps I'm using wrong types, but I strongly doubt: I googled it like a lot and most articles (like this) tells that I'm right.

Error: 0xC0049064 at Import from ODS to DWH, Derived Column [2]: An error occurred while attempting to perform a type cast.

Error: 0xC0209029 at Import from ODS to DWH, Derived Column [2]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[timestamp]" 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 Import from ODS to DWH, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (2) failed with error code 0xC0209029 while processing input "Derived Column Input" (3). 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: 0xC02020C4 at Import from ODS to DWH, OLE DB Source [62]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at Import from ODS to DWH, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLE DB Source returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

  • Explicitly changing types in source (so they match destination) and connecting directly to destination.

Error: 0xC020901C at Direct, OLE DB Source [32]: There was an error with OLE DB Source.Outputs[OLE DB Source Output].Columns[time] on OLE DB Source.Outputs[OLE DB Source Output]. The column status returned was: "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Direct, OLE DB Source [32]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Source.Outputs[OLE DB Source Output].Columns[time]" failed because error code 0xC0209072 occurred, and the error row disposition on "OLE DB Source.Outputs[OLE DB Source Output].Columns[time]" 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: 0xC0047038 at Direct, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLE DB Source returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

  • Data Conversion. Same result: failure.

Error: 0xC02020C5 at Conversion, Data Conversion [2]: Data conversion failed while converting column "time" (74) to column "Copy of time" (11). 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 Conversion, Data Conversion [2]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of time]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of time]" 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 Conversion, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (2) failed with error code 0xC0209029 while processing input "Data Conversion Input" (3). 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: 0xC02020C4 at Conversion, OLE DB Source [62]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at Conversion, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLE DB Source returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

So now I just ran out of tools and ideas how to do this: most instructions just say something like "map this to that and it will work". I'm struggling with this so hard and for so long I even created a StackExchange account for that. Any help appreciated. Cheers.

Best Answer

This message

The value could not be converted because of a potential loss of data.

tells you pretty much everything you need to know. Your problem isn't with the types you're using, it's with the data in your source.

The most likely cause is at least one row of data in your source ODS has values which are in a format that cannot be recast into (for example) a DB_TIMESTAMP.

My recommended approach would be to cast all your source columns explicitly in your SQL source statement.

So instead of 

select time, value from source

do

select (cast time as datetime) as time, (cast value as int) as value from source

In fact, I would run this query against your source table to make sure all the values can be correctly cast into the final fields. (My guess is not or you wouldn't get casting errors.)

Another thing you could do is change your task components from "Fail" on error to "Redirect error rows" and push the error rows into a file destination so you can see which rows are getting kicked out by the transformation component.

Related Topic