Post reply

SSIS Error - export data to sybase db (error code 0xC0047022)

  • March 5, 2010 at 12:13 pm

    #139125

    I am trying to create an SSIS package to transfer data from SQL Server to Sybase db.

    I created a simple data flow task to copy data from a table but I get error at the time of inserting data at the destination. I tried with both VS 2005 and 2008.

    VS 2005

    Error: 0xC0202009 at Data Flow Task, OLE DB Destination [1311]: An OLE DB error has occurred. Error code: 0x80040E2F.

    Error: 0xC0209029 at Data Flow Task, OLE DB Destination [1311]: The "input "OLE DB Destination Input" (1324)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (1324)" specifies failure on error. An error occurred on the specified object of the specified component.

    Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (1311) failed with error code 0xC0209029. 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.

    Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

    VS 2008

    Error: 0xC0202009 at Data Flow Task, OLE DB Destination [73]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E2F.

    Error: 0xC0209029 at Data Flow Task, OLE DB Destination [73]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (86)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (86)" 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: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (73) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (86). 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.

    Does anyone know of a solution?

  • March 5, 2010 at 6:40 pm

    #1129517

    Have you gone into debug mode and set breakpoints?

    Have you tried altering the package or query so that it only inserts 1 record?

    It would appear that you have a mapping issue but you could be violating a Constraint or it could be a data type issue, an OLEDB Provider.

    You could create a simple Table in Sybase that matches up with a SQL Table without constraints as a starting point in an attempt to isolate the problem.

    .

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • March 8, 2010 at 11:29 am

    #1130184

    Welsh,

    Thanks for replying.

    I am able to insert a single row in a new table (in Sybase) but only with all Varchar columns.

    And when I use other datatypes like Datetime/Numeric etc, I get "The data value violates integrity constraints." error. But there are no constraints on the destination table.

    This only happens when I try to pump data using Data Flow Task. I am able to insert same data using Execute SQL task though w/o any constraints violation error.

    I also tried to convert the data before inserting. I am not able to pinpoint the problem as error description is very generic.

    Error: 0xC0202009 at Data Flow Task, OLE DB Destination [3310]: An OLE DB error has occurred. Error code: 0x80040E2F.

    Error: 0xC0209029 at Data Flow Task, OLE DB Destination [3310]: The "input "OLE DB Destination Input" (3323)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (3323)" specifies failure on error. An error occurred on the specified object of the specified component.

    Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (3310) failed with error code 0xC0209029. 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.

    Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

    Any suggestions??

  • March 14, 2010 at 12:17 pm

    #1133422

    I can only say I feel pity about you. The Sybase client software is extremely buggy and incomplete. Until Sybase decides to fix their software there is very little you can do from SSIS.

    What I can recommend is alternative approach. Export the data first to CSV type of format. And then use the Sybase loading tools to process the CSV file.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • March 14, 2010 at 12:41 pm

    #1133425

    I agree with CozyRoc's suggestion.

    I had to import data from a Sybase daily using DTS but unfortunately I do not have currently have access to a Sybase Database that I could try & replicate your problem.

    I have experienced situations in which the Provider was so slow it was faster to export to a file & load into Table.

    If it is not that big of a file then it would not impact you that much with respect to time & performance.

    Do you have SQL Server 2000?

    Just out of curiosity can you import to or from Sybase using the DTS Wizard?

    Sorry I was not any help to you.

    Regards,

    WC

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • March 24, 2010 at 8:50 am

    #1138954

    CozyRoc/Welsh,

    Thanks for the replies. The only problem I had was to insert data in Sybase db (when datattype was not Varchar). Transferring data from Sybase to SQL Server (2005) worked fine.

    I was able to solve this problem by installing the latest drivers for Sybase.

    Thanks again !

  • March 24, 2010 at 11:41 am

    #1139119

    I should have been more specific when I said that it could have been a data type issue, and an OLEDB Provider.

    I ran into the same problem importing from DB2.

    I believe that I identified in the Error Log.

    I looked up the equivalent Data Types between SQL Server & DB2.

    The I used a function to convert the Data to the comparable Data Type.

    When I stated that it could be the OLEDB Provider, I should have been more specific.

    I ran into a situation importing Oracle Data and had to install a newer driver.

    Sorry about that...

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 7 posts - 1 through 6 (of 6 total)

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