SSIS Script task enlist in the current transaction

c# script-task sqlbulkcopy ssis

Question

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
                        sbc.WriteToServer(destination);
                    }
                    myADONETConnection.Close();

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

I appreciate your thoughts.

Vincent

1
0
11/14/2016 4:38:00 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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;
tran.Commit();

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.

0
11/18/2016 12:30:51 AM

Related Questions

Is System.Transactions.TransactionScope compatible with SqlBulkCopy?

SqlBulkCopy... never participates in a deal....SqlCommand... does not do that either. Typical misunderstanding Enlistment takes place at the time...SqlConnection.Open... is known as. Anything that uses that connection after that is inherently a part of the transaction. As a matter of fact, passing an explicit transaction is no longer ...allowed....
.net sqlbulkcopy sql-server transactionscope
asked by usr

When SQLBulkCopy is used with Transaction, an event is fired each time a batch is copied.

Finally, I've chosen to use an internal transaction. As of right now, everything seems to be functioning well; if a mistake is made, the current batch is rolled back. Regarding Ben's earlier remark, I attempted what he suggested, but it isn't feasible since the code doesn't loop through a place where you may trigger an event. In the end, I raised m...
c# sqlbulkcopy sql-server
asked by Hans Rudel

How to handle multiple SQL transactions through different .net(c#) threads

It won't be thread safe to import (DataTable dt in DataTables)....Since sqlConnection already has an active reader from Import, ImportIntoDatabase cannot utilize that connection....Why use several threads if you are locking a table, echo smp?...Use an asynch method like SqlCommand if you wish to build up the input while the SQL inserts are happenin...
.net c# database sqlbulkcopy sqltransaction
asked by paparazzo

Waiting for task to finish before iterating

For this, the async-awqait pattern in C# 5 and.NET 4.5 works well. I notice you've categorized this as.NET4, however there is a really simple method to achieve this if you can utilize the ...Targeting Async Pack...:...void Main() { foreach (string file in Directory.EnumerateFiles(folderPath, "*.xml")) { currentReader = new XmlDataRe...
.net-4.0 c# foreach sqlbulkcopy
asked by Ohad Schneider

Stop SqlBulkCopy from rolling back transaction when one insert fails

Use SqlBulkCopy not....Directly....Inserting first into a temporary table (which also avoids SqlBulkCopy's terrible locking behavior) and then MERGING with the correct rules into the final table are both acceptable techniques....This is how I do all of my bulk updates: quick and easy.
sqlbulkcopy sql-server
asked by TomTom

How to use SqlBulkCopy with transaction on ReliableSqlConnection in sql azure

How much information is being sent in bulk? Instead of using Bulk Copy, you may be able to utilize a Table-Valued-Parameter strategy instead, which is easy to incorporate in a transaction. The client-built table is swiftly passed to SQL as a parameter, and server-side inserts are carried out using BULK optimizations. This is suitable for rows with ...
azure sqlbulkcopy sql-server transactions
asked by Stuart Ozer

script task bulkcopy from Excel to Sql Server 2008. Missing row

Okay, I changed my approach and was able to resolve issue. Everything is now flawless....Instead, I did the following using a data adapter. I still don't understand why the earlier code didn't function properly....adapter = new OleDbDataAdapter ExcelDataQuery, OleDbConn; OleDbDataAdapter; adapter Fill(dataTable);... SqlBulkCopy bulkcopy ...
excel excel-2007 sqlbulkcopy sql-server ssis
asked by Bluffer

SqlBulkCopy inserts when inside a transaction prevents any other writes on a table

The ...IsolationLevel... pertains solely to reads, not writes. The second client won't be able to write simultaneously if one of your clients is writing data, but it should be able to read the data as it was before the transaction began.
asp.net c# sql sqlbulkcopy sql-server
asked by Alex

psycopg2 copy_from raises asynchronous ProgrammingError with sync connection in celery task

We encountered similar problem in pgcli, where it was discovered that a wait callback was causing the connection to seem to be asynchronous to psycopg2. This aided in...from contextlib import contextmanager @contextmanager def _paused_thread(): try: thread = psycopg2.extensions.get_wait_callback() psycopg2.extensions.set_wait_c...
asynchronous celery psycopg2 python sqlbulkcopy
asked by Catherine Devlin

SSIS - OleDB connection not updating data, inserted thru SQLBulkCopy with SQL Connection

My bad....I was sending the data type as Varchar rather than as long (int in SQL)....As soon as I ask a question here after searching for a few hours, the thought occurs to me to verify the data type....I hope someone will benefit.
oledbconnection sqlbulkcopy sqlconnection ssis
asked by Manish Joisar

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

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