[SSIS]使用第三方ODBC导入数据时出现错误DTS_E_INDUCEDTRANSFORMFAILUREONERROR

 Microsoft is working to document this problem officially and explore the
options on how to proceed with this . The KB number is in draft currently,
and hope it will be up in a few weeks at the link
http://support.microsoft.com/?id=969845

Here is my unofficial explanation:
===========
After installing the .Net Framework 3.5 Service Pack 1 (KB 951847) or .Net
Framework 2.0 Service Pack 2, SSIS packages utilizing some 3rd party ODBC
driver to import character strings or byte data may fail with the message:


[ADO NET Source [1424]] Error: SSIS Error Code
DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The

"component "ADO NET Source" (1424)" failed because error code 0x80131937
occurred, and the error

row disposition on "output column "columnname" (<number>)" 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 0xc0209029: Data Flow Task: The "component "<yourcomponentname>" (1)"
failed because error code 0x80131937 occurred...

[ADO NET Source [1424]] Error: The component "ADO NET Source" (1424) was
unable to process the data. Pipeline component has returned HRESULT error
code 0xC0209029 from a method call.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The
PrimeOutput method on component "ADO NET Source" (1424) returned error code
0xC02090F5. 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.



Package Design Scenarios that may be affected:
==============
Referencing string columns, and Byte/Image columns in the following ways:
SSIS 2005 Data Reader source -> System.Data.ODBC --> 3rd party ODBC driver
SSIS 2008 ADO.Net Source -> System.Data.ODBC --> 3rd party ODBC driver
SSIS 2008 ADO.Net Destination -> System.Data.ODBC -> 3rd party ODBC driver

Most Common Errors you might see:
==============
DTS_E_INDUCEDTRANSFORMFAILUREONERROR or 0x80131937


Some high level root cause Details:
==============
In the .Net framework Service packs mentioned, there were changes in the
Managed ODBC provider (System.Data.Odbc) to optimize the memory consumed for
variable length strings and byte/images values when those data types were
read from the native ODBC driver underneath the managed adapter. Prior to the
service pack, variable length strings and byte data in the ODBC driver could
cause higher than desired memory consumption as results were cached
wastefully. An unintended side effects of this optimization was that some
existing ODBC drivers did not handle the new approach as expected, and
therefore returned errors instead of returning the string data as Microsoft
had expected.

This problem affects the ODBC drivers accessed from the managed .Net ODBC
Data Provider (System.Data.ODBC) when specific settings are used with the
client application, such as SQL Server Integration Services (SSIS).

Those settings are:
{
1. ODBC ExecuteReader called with parameter
CommandBehavior.SequentialAccess.

This call and setting is used when SSIS is reading data from a Managed
ADO.Net (2008) or DataReader (2005) source, and when that source is NOT
configured to redirect Error and Truncated values.


AND
2. OdbcDataReader.IsDbNull is called.
SSIS uses this method to determine if the value in a character field is null
or not.

AND
3. At least One of the following:
A. OdbcDataReader.GetBytes is called.
SSIS uses this method for getting certain byte and image data types from the
managed ODBC provider.
OR
B. OdbcDataReader.GetChars is called.
SSIS uses this method for getting certain variable length string data types
from the managed ODBC provider.
}

The primary change causing some ODBC drivers to report failure was that the
System.Data.ODBC managed provider calls into the native ODBC driver using the
API SQLGetData, using a buffer of size 0.

Normally ODBC Compliant drivers do not normally have a problem with this
call, but non-compliant drivers may fail, thus causing the symptoms noted
above. In the compliant scenario, SQLGetData should return SUCCESS_WITH_INFO
when a size zero buffer is detected. In the non-compilant scenarios, NO_DATA
is the return value, and SSIS raises the errors as shown above.


Possible Workarounds
==============
1. Perhaps the easiest workaround for this problem is to edit the SSIS
Package design to configure Error and Truncation Redirection on the
DataReader or ADO.Net Source.

By doing so, SSIS will no longer use the managed ODBC provider property
CommandBehavior.SequentialAccess and should no longer be aggravated by this
service pack change in System.Data.ODBC

Modify the SSIS package to redirect the DataReader Error Output to a text
file or error table, thereby allowing the package to succeed even though no
rows are written out to the error output file.

For Example:
A. Open the failing SSIS Data Flow Task.

B. On DataReader Source Properties click the ‘Input and Output Properties’
Tab
Click and expand the "DataReader Output" in the "Inputs and outputs" tree on
the left.
Click and expand the Output columns Folder icon in the tree.

C. Click on each column in the list of Output Columns and note the DataType
property.
For each that is DataType DT_WSTR, DT_NTEXT, DT_BYTES, or DT_IMAGE,

Edit the two properties:
1. 'ErrorRowDisposition' RD_RedirectRow
2. 'TruncationRowDisposition' RD_RedirectRow
OK to save the changes.

D. In the Data Flow design surface, place a new destination onto the
surface. You may choose a SQL Table to save the rows, or flat file (text or
..cvs file), or a connection of your choice.

Use the Red arrow from the failing source to connect it to the destination.
This will direct the Error rows and Truncation Rows to an destination to save
the error rows.

E. Repeat A-D for all Data Flow Tasks that access ODBC strings/image
columns and fail with the above errors.

=======
2. Another workaround may be to uninstall the service pack .Net Framework
3.5 SP1. This may be a quick fix in an emergency, but in general it is
undesirable to uninstall service packs, since it sacrifices improved
reliability, feature improvements, and performance optimizations that the
service pack offers.

http://blogs.msdn.com/astebner/archive/2008/08/01/8803442.aspx
http://blogs.msdn.com/dougste/archive/2008/08/19/uninstalling-net-framework-3-5-sp1.aspx
=======

3. You may also be able to contact the ODBC driver vendor to see if the
vendor has any update for their driver to allow it to handle the ODBC call
using zero buffer length to SQLGetData, thereby allowing variable buffer
sizing using return value SUCCESS_WITH_INFO and the size of the variable
length data.

Several vendors have taken the suggestion and started working to improve the
way the driver handles this condition.
=======

Best of luck, Jason H

 

http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.dts/2009-04/msg00016.html