Post reply

SSIS Transaction Error

  • April 19, 2017 at 3:59 am

    #337320

    I have a Zip File which contains .CSV files .Each .CSV will be mapped to respective Sql Server Table.

    I have enabled transaction option to required once the files are unzipped .If any thing goes wrong entire zip contents should be rolled back.

    Following are the steps which are followed in building ssis Packagae

    1. Unzip the file
    2. Seq container Transaction Option set =required Isolation level :Serializable

            2.1.  Delete data in tbl1,tb12,tb13 2.1 Iterate through csv files which has tb2.csv ,tbl2.csv,tbl3.csv
           2.2.  DFT - Based on table name csv willto tb1,tb2.tb3 of sql server (OLEDB dest)

    Note: In Oledb destination

    1.Table/view Fast load option is set
    2.Identity are checked in 
    3.Table Lock is not checked

    I am getting below error : Hresult: 0x80004005 Description: "This operation conflicts with another pending operation on this transaction. The operation failed.".

    Please suggest a way to overcome this error.

  • April 19, 2017 at 5:51 am

    #1938767

    Try changing 'Table/view Fast load' to 'Table/view' and see whether that helps.

    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

  • April 19, 2017 at 8:05 pm

    #1939000

    If i use table option performance is very slow...I have to delete  and insert records in same transaction. is this causing issue...if so please how to.define transactions in this scenario.everytime i have to delete around 20k records and insert again into the table

  • April 20, 2017 at 6:57 am

    #1939058

    greeshatu - Wednesday, April 19, 2017 8:05 PM

    If i use table option performance is very slow...I have to delete  and insert records in same transaction. is this causing issue...if so please how to.define transactions in this scenario.everytime i have to delete around 20k records and insert again into the table

    If you are removing all data from these tables, why not use TRUNCATE instead of DELETE?

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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