CODEWITHSUNDEEP
sql-server-2008t-sqliisbids
Tod
Tod

Reputation: 8252

How do I use manual T-SQL transactions in BIDS with IIS?

I'm as green as they come when using BIDS and SSIS, just started today. I wanted to create a table and import some data into from an Access data file. I got that to work but I had to keep deleting the partial results and I thought wrapping it all in a Transaction would be a good idea. I'm not running MSDTC so I thought I would just use a manual transaction. I tried adding an Exectute T-SQL Task that contained BEGIN TRANS then I added two more one with COMMIT TRANS and one with ROLLBACK Trans. If the Data Flow Task failed it went to rollback otherwise it went to commit. When it got to the commit, it always said there was no begin transaction. The package looks like this. Is this supported and I'm just missing setting some magic property to make it work?

enter image description here

Upvotes: 1

Views: 433

Answers (2)

HLGEM
HLGEM

Reputation: 96658

OK, here is my advice on how to appraoch this. I usually transform teh data so that no rollback is ever needed before I do a data flow to load to prod. SO I load to a staging table and tehn either clean the data or delete bad records or check for teh existance of somethign that would cause a rollback before I ever consider putting something inot a produuction table. If you have showstoppers they should happen long before you get to production. If you simply want to find and deal with the mallformed records, teh staging table will allow you to do that, send the bad data to an exception table etc, all without disturbing prod. When you have the final data flow to prod, everyting is know to be good at that point. We do thousands of imports and never need to rollback at the prod level.

If you really, really want to rollback at the prod level, you can do the import in an execute SQl task, after using the dataflow to populate the staging table, that is a stored proce with an explicit transaction and a try catch block to rollback in case of error.

Upvotes: 1

zahidhanif
zahidhanif

Reputation: 56

As each of these tasks are run independently, this is why your begin/rollback/commit will not work. If you just want to delete all the data in the table, run a TRUNCATE statement on the table when the data flow task fails. When loading like this, I usually clear the table and populate it each time to test the data flow task.

Upvotes: 1

Related Questions