SSIS Script task enlist in the current transaction

c# script-task sqlbulkcopy ssis

Question

I have a package in SSIS with multiple task. I am loading file,if the Filesystem task at the end fail i want to be able to rollback the transaction.My package look like that.general_package I like to be able to rollback all the operations the SSIS script have been done.To do that i need the SSIS script to enlist in the transaction created by the BEGIN_TRANSACTION Sql task.How could i do that ?

In ssis to gather the transaction i do :

 object rawConnection = Dts.Connections["destination_ado"].AcquireConnection(Dts.Transaction);
             myADONETConnection = (SqlConnection)rawConnection;

Then i do a BulkCopy:

using (SqlBulkCopy sbc = new SqlBulkCopy(myADONETConnection))
                    {
                        sbc.DestinationTableName = "[" + SCHEMA_DESTINATION + "].[" + TABLE_DESTINATION + "]";
                        //  sbc.DestinationTableName = "test_load";
                        // Number of records to be processed in one go
                        sbc.BatchSize = 10000;


                        // Finally write to server
                        sbc.WriteToServer(destination);
                    }
                    myADONETConnection.Close();

How do I tell the SqlBulkCopy to use the existing transaction ? In the options of the connection in SSIS i use RetainSameConnection:true

Thanks for all your thought

Vincent

Popular Answer

So i found a solution. On the first Script block (extract and load )i create a transaction with this code:

SqlTransaction tran = myADONETConnection.BeginTransaction(IsolationLevel.ReadCommitted);

Then i use this transaction in the SqlBulkCopy this way :

using (SqlBulkCopy sbc = new SqlBulkCopy(myADONETConnection,SqlBulkCopyOptions.Default,tran))

Pass the transaction object to an SSIS variable :

Dts.Variables["User::transaction_object"].Value = tran;

Then on my two block at the end Commit transaction and Rolloback transaction i use SSIS script, read the variable and either commit or rollback the transaction:

SqlTransaction tran = (SqlTransaction)Dts.Variables["User::transaction_object"].Value;
tran.Commit();

As a result if a file cannot be move to the Archive folder i don't get load twice,a transaction is fire for each file so if a file can't be more only the data about this file get rollback and the enumerator keep on going to the next one.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why