Archive

Archive for the ‘SSIS’ Category

DTS_E_INDUCEDTRANSFORMFAILUREONERROR

July 5, 2011 4 comments

Last week I bumped into another peculiar problem: SSIS 2005 Data Reader Source was failing with some weird errors:

[Source – Query [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “component “Source – Query” (1)” failed because error code 0x80131937 occurred, and the error row disposition on “output column “logid” (22)” 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.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread “SourceThread0” has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread “WorkThread0” has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

After spending some time bumping my head around what might be the cause, I had no choice left as to… start browsing the i-net. Apparently, there are many posts out there going on about this DTS_E_INDUCEDTRANSFORMFAILUREONERROR situation, but I found only two useful ones, which did lead to the cause: Here and here. The latter link, though, appears dead, as for the July 5th, 2011.

Back on the track, here is the thing: whenever you have SQL Server 2005, 2008 or 2008 R2 with .NET Framework 2.0 SP2 or .NET Framework 3.5 SP1 installed on your box, you’re most likely to run into this problem using ODBC with the DataReader Source. All the reasons are described in the post by the first link, I will simply go with the solution screenshots.

First, find all character type columns in the source/destination table:


SELECT t.name, c.name, tp.name
FROM sys.tables t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
WHERE t.name = 'cms_haglog'
AND tp.name IN ('varchar', 'char', 'nvarchar', 'nchar')
ORDER BY c.column_id

Then open DataReader’s Advanced Editor

Go to Input and Output Properties bookmark,

expand DataReader Output -> Output Columns,

click on the first character column (DT_WSTR, as it appears in the right pane) and change ErrorRowDisposition and TruncationRowDisposition from RD_FailComponent to RD_RedirectRow

Do the same re-mapping for the rest of the char-columns, if there are more.

After that, edit the DestinationConnectionOLEDB

Go to Error Output and change Fail component to Redirect row under Error in the right pane

Pay attention that DestinationConnection remained faulty, with “The error row disposition on input Destination Input (257) cannot be set to redirect the row…” message.

Ignore the message, it’s totally hintless. The small thing which is left to be done is alter FastLoadMaxInsertCommitSize from 0 to, say, 1000.

That’s it. Now the package will run.

P.S.

Also works as a solution for

[Flat File Source [25]] Error: Data conversion failed. The data conversion for column returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page.”.

Advertisements