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