SSIS Script task enlist in the current transaction

Question

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. 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

Vincent Diallo-Nort

View more on Stack Overflow

Vincent Diallo-Nort · Accepted Answer · 11/18/2016 12:30:51 AM

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.

SSIS Script task enlist in the current transaction

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator