SSIS Script task enlist in the current transaction

c# script-task sqlbulkcopy ssis


In SSIS, I have a package with numerous tasks. If the Filesystem job at the end fails when I'm loading a file, I want to be able to roll back the transaction. That's how my package looked. general_package I enjoy having the option to undo every action the SSIS script has taken. I need the SSIS script to join the transaction started by the BEGIN TRANSACTION SQL job in order to do that. How am I able to achieve that?

To aggregate the transaction in SSIS, I

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

I then 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

How can I instruct SqlBulkCopy to use the current transaction? I use RetainSameConnection in the SSIS connection options: true

I appreciate your thoughts.


11/14/2016 4:38:00 AM

Popular Answer

So I came upon a fix. I write the following code to establish a transaction on the first script block (extract and load):

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

When using SqlBulkCopy, I then utilize this transaction as follows:

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

An SSIS variable should get the transaction object:

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

Then, using SSIS script, I utilize my two blocks' end Commit Transaction and Rolloback Transaction buttons to choose whether to commit or rollback the transaction:

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

Because each file is subject to a separate transaction, if a file cannot be moved to the Archive folder, just the data related to that file is rolled back, and the enumerator moves on to the next file.

11/18/2016 12:30:51 AM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow