December 13, 2011 at 8:58 am
Trying to move data from SQL server to Informix via SSIS.
Source Column is: SQL Server
ID bigint
Destination Column is: Informix
ID integer
Using data conversion (string(DT_STR)) Length: 12 between source and destination in the flow.
Getting the following error message:
SSIS package "nrrs_notes.dtsx" starting.
Information: 0x4004300A at NOTE_PRODUCER, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at NOTE_PRODUCER, SSIS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at NOTE_PRODUCER, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at NOTE_PRODUCER, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at NOTE_PRODUCER, SSIS.Pipeline: Execute phase is beginning.
Error: 0xC020844B at NOTE_PRODUCER, ADO NET Destination [40]: An exception has occurred during data insertion, the message returned from the provider is: ERROR [42000] [Informix][Informix ODBC Driver][Informix]A syntax error has occurred.
Error: 0xC0047022 at NOTE_PRODUCER, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ADO NET Destination" (40) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (43). 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 NOTE_PRODUCER, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x4004300B at NOTE_PRODUCER, SSIS.Pipeline: "component "ADO NET Destination" (40)" wrote 0 rows.
Information: 0x40043009 at NOTE_PRODUCER, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: NOTE_PRODUCER
Warning: 0x80019002 at nrrs_notes: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "nrrs_notes.dtsx" finished: Failure.
I tried googling the error codes: 0xC020844B & 0xC0047022, but can't find any fixes / workarounds for this data conversion issue.
July 9, 2012 at 1:37 pm
Hi tan110,
did you find a solution for this problem?
I have an identical situation, reading from informix works without a problem. Inserting with SQL-Task directly works, too! But no way to insert data via ADO.NET-Destination to informix (here 7.31).
We tried different drivers (3.33, 3.50, 3.70), but nothing works. :crazy:
July 9, 2012 at 1:48 pm
What's the largest data value you are trying to convert? The Informix type may need to be INT8 in order to handle it.
http://www.ehow.com/info_8738720_informix-data-types.html
July 9, 2012 at 2:20 pm
I created a simple test-table on informix with only one varchar(10) column, to avoid problems with date-conversion, digits, ... But even this simple task does not work.
By the way, the executing local system is Windows 7 64Bit.
Would be interesting to know how the insert statement looks like when performing the task, but I found no way to do this.
Here is the complete output:
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x80049304 at Data Flow Task, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.
Error: 0xC020844B at Data Flow Task, ADO NET Destination [19]: An exception has occurred during data insertion, the message returned from the provider is: ERROR [42000] [Informix][Informix ODBC Driver][Informix]A syntax error has occurred.
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ADO NET Destination" (19) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (22). 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 Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "ADO NET Destination" (19)" wrote 0 rows.
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
July 9, 2012 at 2:38 pm
By the way, another test I made was writing the source to flat file, which worked.
After that, I read from the flat file, trying to insert to informix... same problem (did not work).
May 27, 2014 at 2:38 am
We resolve this problem by installing the latest driver (http://dev.mysql.com/downloads/connector/net/) from mysql i.e. 6.8.3 (Connector /NET). Hope it helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply