Run SSIS package with One Execute exe task as a transaction

Question

Run SSIS package with One Execute exe task as a transaction

happy55

Ten Centuries

Points: 1243
More actions
May 3, 2017 at 1:00 pm

#402023

I have a SSIS package which has just one step which executes a C# exe.
I want to execute the package as one transaction so that if any error happens when the exe is executed incomplete data gets rolled back.
I am using Sql server 2012.
Thanks,

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

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

I'm not quite sure what you're trying to accomplish, but offhand it would seem the SSIS package would have nothing to do with any database transaction in this case. You'd have to manage the database transaction in the C# code to ensure it was truly one transaction. What does the C# code do to the database?

happy55 Ten Centuries Points: 1243 More actions · Answer 2

Chris Harshman - Wednesday, May 3, 2017 3:17 PM
I'm not quite sure what you're trying to accomplish, but offhand it would seem the SSIS package would have nothing to do with any database transaction in this case. You'd have to manage the database transaction in the C# code to ensure it was truly one transaction. What does the C# code do to the database?

Thanks .The C# code does a lot of stuff, it inserts/deletes/updates records to few tables in database. So adding transaction at the SSIS package will not help?

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 3

happy55 - Thursday, May 4, 2017 4:05 AM
Chris Harshman - Wednesday, May 3, 2017 3:17 PM
I'm not quite sure what you're trying to accomplish, but offhand it would seem the SSIS package would have nothing to do with any database transaction in this case. You'd have to manage the database transaction in the C# code to ensure it was truly one transaction. What does the C# code do to the database?
Thanks .The C# code does a lot of stuff, it inserts/deletes/updates records to few tables in database. So adding transaction at the SSIS package will not help?

I don't believe this would work, I agree with Chris, the transactions would need to be handled in your C# app. Alternatively have SSIS handle all of the data processing and then you can control it all in a single transaction. I believe that you could make use of either the Windows Distributed Transaction Coordinator by setting the transaction property for the parent object or begin an SQL Transaction in an Execute SQL Task, run all of the data processes and then either Rollback on a task failure or commit on success. All of these tasks would need to re-use the same connection instead of the connection closing and re-opening for each task.

happy55 Ten Centuries Points: 1243 More actions · Answer 4

tim.ffitch 25252 - Thursday, May 4, 2017 4:26 AM
happy55 - Thursday, May 4, 2017 4:05 AM
Chris Harshman - Wednesday, May 3, 2017 3:17 PM
I'm not quite sure what you're trying to accomplish, but offhand it would seem the SSIS package would have nothing to do with any database transaction in this case. You'd have to manage the database transaction in the C# code to ensure it was truly one transaction. What does the C# code do to the database?
Thanks .The C# code does a lot of stuff, it inserts/deletes/updates records to few tables in database. So adding transaction at the SSIS package will not help?
I don't believe this would work, I agree with Chris, the transactions would need to be handled in your C# app. Alternatively have SSIS handle all of the data processing and then you can control it all in a single transaction. I believe that you could make use of either the Windows Distributed Transaction Coordinator by setting the transaction property for the parent object or begin an SQL Transaction in an Execute SQL Task, run all of the data processes and then either Rollback on a task failure or commit on success. All of these tasks would need to re-use the same connection instead of the connection closing and re-opening for each task.

Thanks. All the data processing as well as the logic is inside the C# code and not separate .The requirement is such that it cannot be separate.

happy55 Ten Centuries Points: 1243 More actions · Answer 5

Thanks all for the reply. Is there a way to handle transactions in SSIS package when all the data processing is done in C# exe?
Any help is much appreciated.
I set the whole package with transaction property required and the C# exe task as transaction property supported.
My C# exe threw an error but the inserts/updates done to tables by the C3 exe before the error occurred were not rollback.

Thanks

Phil Parkin SSC Guru Points: 246962 More actions · Answer 6

happy55 - Tuesday, May 9, 2017 10:12 AM
Thanks all for the reply. Is there a way to handle transactions in SSIS package when all the data processing is done in C# exe?
Any help is much appreciated.
I set the whole package with transaction property required and the C# exe task as transaction property supported.
My C# exe threw an error but the inserts/updates done to tables by the C3 exe before the error occurred were not rollback.
Thanks

I don't think so. Do the transaction handling in C#.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin