Hopefully someone can help me with this. I have an SSIS package that
runs great in all my environments. However, this package recently
stopped working in my production environment. If I export the package
to any other environment it runs great. In prod, the error below
shows up and it will not run. Does anyone have any idea what the
problem is? This is a clustered environment while the others are
not. Any insight would be great. The main purpose of this package is
to take data from an AS400 and bring into a SQL DB.

Thanks for any help!


Error: 0xC0209029 at ItemMsgs, ITEMMSGSN2 [1]: SSIS Error Code
DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component
"ITEMMSGSN2" (1)" failed because error code 0x80004003 occurred, and
the error row disposition on "output column "MSGCODE" (28)" 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 at ItemMast, ITEMMASTN2 [173]: SSIS Error Code
DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component
"ITEMMASTN2" (173)" failed because error code 0x80004003 occurred, and
the error row disposition on "output column "DESCR" (1082)" 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: 0xC02090F5 at ItemMast, ITEMMASTN2 [173]: The component
"ITEMMASTN2" (173) was unable to process the data.
Error: 0xC02090F5 at ItemMsgs, ITEMMSGSN2 [1]: The component
"ITEMMSGSN2" (1) was unable to process the data.
Error: 0xC0047038 at ItemMast, DTS.Pipeline: SSIS Error Code
DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component
"ITEMMASTN2" (173) 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.
Error: 0xC0047038 at ItemMsgs, DTS.Pipeline: SSIS Error Code
DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component
"ITEMMSGSN2" (1) 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.
Error: 0xC0047021 at ItemMsgs, DTS.Pipeline: 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.
Error: 0xC0047021 at ItemMast, DTS.Pipeline: 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.
Error: 0xC0047039 at ItemMast, DTS.Pipeline: SSIS Error Code
DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown
signal and is terminating. The user requested a shutdown, or an error
in another thread is causing the pipeline to shutdown. There may be
error messages posted before this with more information on why the
thread was cancelled.
Error: 0xC0047039 at ItemMsgs, DTS.Pipeline: SSIS Error Code
DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown
signal and is terminating. The user requested a shutdown, or an error
in another thread is causing the pipeline to shutdown. There may be
error messages posted before this with more information on why the
thread was cancelled.
Error: 0xC0047021 at ItemMast, DTS.Pipeline: 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.
Error: 0xC0047021 at ItemMsgs, DTS.Pipeline: 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.

Same here!!!
I have sp3 on production and sp2 on test and the package stopped working
after i installed sp3 on sql server 2005 in production!!! Why is that? it
cannot even be un installed so i would need to reinstall the whole sql server
2005 in case there's no solution!!! PAIN IN THE A..!!!

"Chris" wrote:

I was having the same problem. I was trying to pull data from mysql server
to my SQL Server.

I found it to be that the column trying to pull over was set to '' (not
null, but blank).
I changed my pull query to treat '' as null.
select case when <col> = '' then null end as <col> from <table>
Luckily this is OK in my scenario, but isn't necessarily always going to be
okay.

I found it to be what Enrico said. This server has the sql server service
pack 3 whereas my other server doing the exact same pull does not yet have it.

I tell you, this thing is driving me nuts. I have a server that is
running the same SQL service pack level and can run the package fine.
The ODBC driver I am using is the same on both machines. The windows
OS is server 2003 r2 on both machines. The only differance is that
the server that is working is a hp dl380 g4 and the one that is not
working is a dl 380 g5.

Also,

I have one data flow that contains a couple of different source-to-
destination transformations. If I remove all of the source-to-
destination transformations expcept for one, the data flow will
execute successfully. The moment I add another stream to the data
flow, it blows up. Each are hitting seperate tables, so there should
be any blocking.

It seems like I cannot run multiple threads at once. I am not sure
where the problem lies.

Any ideas

Looks like I found the problem. I uninstalled .net 3.5, 3.0 and 2.0.
I reinstalled .net 2.0 and the package ran well.

To be more specific, the problem occurs with .net framework SP2 is
installed. II have reverted back to SP1 and everything is fine.
There must be a bug in SP2.

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/archi...1/8803442.aspx
http://blogs.msdn.com/dougste/archiv...k-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

--------------------------------------------------------------------------------
Didn't get enough help here? Submit a case with the Microsoft Customer
Support team for deeper investigation -
http://support.microsoft.com/select/...get=assistance

On Apr 2, 11:44*pm, Jason H <Jas... (AT) discussions (DOT) microsoft.com> wrote:
I just wanted to reaffirm this ... I have had same issue in SSIS
with a MySQL 4.0.26 Client and a SQL Server 2005 destination and it
was giving error on the DBTYPE_DB_TIMESTAMP field ... I also had to
reapply the .NET 2.0 and .NET 2.0 SP1 and do the error redirect
programming method in above post. It really doesnt redirect any rows
to error but allows the process to continue when an error is
reached.

For me it would exhibit itself immediately or on one of the early rows
of data (3751 rows) depending on how I had the VALIDATEMETADATA set
(True or False).

Thanks,

Bob Williamson

I have the same problem as the rest of the group, and i am trying to
read from MySQL (imagine that).
I have implemented the work around as suggested in the KB article,
http://support.microsoft.com/kb/969845.
After giving the DataReader a error row file destination, i put it in
production. The DTS_E_INDUCEDTRANSFORMFAILUREONERROR disappeared, and it
seems to have helped. But now i am having another read problem, looks
like the message just changed, not sure.
Now i get: "The component "DataReader Source Pageviews" (238) was unable
to process the data." This could be because of the limitations of MySQL
availability, or could be related to the .NET framework SP2 issue. I
have not reinstalled it yet.
When i get further information on this issue i will reply to this
thread.
Hopefully someone else will have another method of solving the secondary
problem, "unable to process the data".
Thansk.


*** Sent via Developersdex http://www.developersdex.com ***

I have the same problem as the rest of the group, and i am trying to
read from MySQL (imagine that).
I have implemented the work around as suggested in the KB article,
http://support.microsoft.com/kb/969845.
After giving the DataReader a error row file destination, i put it in
production. The DTS_E_INDUCEDTRANSFORMFAILUREONERROR disappeared, and it
seems to have helped. But now i am having another read problem, looks
like the message just changed, not sure.
Now i get: "The component "DataReader Source Pageviews" (238) was unable
to process the data." This could be because of the limitations of MySQL
availability, or could be related to the .NET framework SP2 issue. I
have not reinstalled it yet.
When i get further information on this issue i will reply to this
thread.
Hopefully someone else will have another method of solving the secondary
problem, "unable to process the data".
Thanks.


*** Sent via Developersdex http://www.developersdex.com ***