Post reply

ProcessInput Method Fails with Error Code 0xC0202009

  • January 3, 2007 at 1:08 pm

    #116750

    Hello,

    I have a Data Flow Task within an SSIS package where data is pulled from a Fixed Width flat file, goes through a conditional split, and sends data either to a SQL Server 2000 table, or to another fixed width flat file.

    The first condition in the conditional split checks for the following; [Column 44] + [Column 45] != "D1" . If this is true, then those rows go to a SQL Server 2000 table destination. If false, then the rows go to another flat file.

    The SQL Server 2000 table has a unique nonclustered index with ignore_dup_key. From what I can tell, it looks like the step fails when a duplicate record is encountered. However, it appears that the unique records still go into the table.

    When I check the 'Progress' tab of the package, the following steps seem to be suspect;

    [Populate Staging [786]] Information: The final commit for the data insertion has started.

    [Populate Staging [786]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80004005  Description: "Duplicate key was ignored.".

    [Populate Staging [786]] Information: The final commit for the data insertion has ended.

    [DTS.Pipeline] Error: The ProcessInput method on component "Populate Staging" (786) failed with error code 0xC0202009. 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.

    [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0202009.

    [DTS.Pipeline] Information: Post Execute phase is beginning.

    ..and..

    Task 2 Isolate Suspect Records and Insert Known Good Records failed

    In one case, the source flat file had 704,101 rows. Each row tested true in the conditional split, and all but one row made it into the SQL Server table. The step failed, and the 'Progess' section containted the entries mentioned above.

    The Connection Manager for the SQL Server table destination uses the Native OLE DB\Microsoft OLE DB Provider for SQL Server.

    Is the 'error' that occurs when a duplicate row is encountered causing the Data Flow task to fail, or could it be something else?

    Thank you for your help. Please let me know if you need more information.

    CSDunn

  • January 4, 2007 at 8:12 am

    #681206

    Here is some input that I recieved from one of the MSDN forums today;

    **************************

    The error from the destination is causing the whole dataflow to fail.

    one can shunt failed rows from the oledb destination to some other repository (like a file) using and configuring the "error output" that is hanging off of the destination component. you can configure the component to not fail when inserts fail.

    ***************************

    Does anyone else have something to add to this?

    Thanks!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply