Post reply

A commit failed

  • February 8, 2011 at 7:33 pm

    #246390

    I have setup a simple testing for dealing with new data, in which a comparison is performed if the field (ID) is found

    on the base table (tableBase) then UPDATE else INSERT.

    A Data Flow on the Control Flow

    New data - OLD DB Source data on a tableNewData

    To lookup - Lookup between base table on tableBase and tableNewData with two outputs to classify INSERT (green)/UPDATE (error)

    INSERT - OLE DB Command for inserting data to tableBase

    UPDATE - SQL Server Destination for updating data to tableBase

    Therefore both the INSERT and UPDATE are on the same tableBase.

    All objects are on the same database.

    When there are both INSERT and UPDATE from new data, it is running ok, but when there is only

    UPDATE and no INSERT I got the error message "A commit failed".

    Any advice.

    Thank you.

  • February 9, 2011 at 2:45 am

    #1284289

    Is it possible to give all the exact error messages?

    Maybe you could try to write the updates to a staging table and do the updates after the data flow in an Execute SQL Task.

    That way you avoid the OLE DB Command, you work with a set based approach and you avoid possible concurrency on your destination table.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 9, 2011 at 8:55 am

    #1284551

    Thanks.

    The error message is

    [SQL Server Destination [794]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

    [SQL Server Destination [794]] Error: A commit failed.

    You might be right, I just try to avoid another staging table.

  • February 9, 2011 at 8:57 am

    #1284555

    Are you trying to access data from a linked server?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • February 9, 2011 at 9:00 am

    #1284559

    No, I do not have a linked server. All INSERTS and UPDATES are aiming to the same table/database/server/machine.

    Thanks.

  • February 9, 2011 at 11:21 am

    #1284707

    Is the Integration Services service running on the same server as the destination table?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 9, 2011 at 11:55 am

    #1284740

    Everything on the same box.

    Thanks.

  • March 23, 2012 at 1:46 am

    #1463207

    I am facing same problem.What was the solution for this problem.

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

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