Transaction Control in SSIS

Question

Transaction Control in SSIS

raga.bysani

Old Hand

Points: 375
More actions
August 29, 2013 at 1:19 am

#298270

Hi Team
Require your help on TRANSACTION CONTROL IN SSIS ,
We have created package with 12 dataflow task in one sequence container based on the sequence. All the dataflow task has SSIS framework implemented , when ever a record does not match certain condition that particular record moves into the error/reject log table but package does not fail it moves to the next record ,
However my requirnment is if any record move to error/reject log table than that particular record to be rolled back & cannot commit and then pick another record.
Package will not fail but rollback has to be implemented for all the records that get captured in error/reject log table
Thanks & Regards
Raga

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 1

Rollback how? If a row is sent to an error output, it is not written to the destination.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

raga.bysani Old Hand Points: 375 More actions · Answer 2

Thank you ,

we have 12 dataflows in the sequence container , lets say records were inserted in 7 dataflow task and failed in the 8th dataflow task & the record is written in the error/reject log table , such record should be rolled back from the previous dataflow task.

Apologies if not clear

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 3

I don't think that is possible.

Rows are moved in batches, and it's an all or nothing transaction.

If you want to implement something like this, you'll need to do the clean-up yourself.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

psingla Hall of Fame Points: 3840 More actions · Answer 4

Following steps might be helpful:

1.What I think is put all the task that you want to rollback in one transaction using required followed by supported

2. bit put the transaction option as "NOTSUPPORTED" for task which is inserting records in error log.

Although i have not tested it.I suggested it based on concepts

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

raga.bysani Old Hand Points: 375 More actions · Answer 5

Thank for reply ,

We are moving record by record using for each loop container

But will this work without the package being failed , when ever there is error that record move to error table & packages does not fail , it moves to next record.

Thank

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 6

raga.bysani (8/29/2013)
Thank for reply ,
We are moving record by record using for each loop container
But will this work without the package being failed , when ever there is error that record move to error table & packages does not fail , it moves to next record.
Thank

Wow, that must be slow 🙂

If you are moving record by record and you have each time the same destination, you could start a transaction yourself each time you transfer a record. Put a Execute SQL Task at the start of the loop with a BEGIN TRAN statement. Inside the data flow, check with the rowcount tran if a row was sent to the error log. After the data flows, put a COMMIT TRAN on the success path with an expression on the precedence constraint to check if no rows went to the error log. Put an Execute SQL Task with ROLLBACK TRAN on the success path but this time with a precedence constraint @RowCount > 0 (rows went to the error log).

This way you create a transaction for each individual row without failing the package. Make sure the connection manager is set to RetainSameConnection = True.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

raga.bysani Old Hand Points: 375 More actions · Answer 7

Thanks for the reply,

We have also followed the same procedure, but we are getting error while implementing rollback as the we are using execute package tasks(i.e 3 child packages) between begin tran and child tran.

[Execute SQL Task] Error: Executing the query "begin tran" failed with the following error: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Can any one help