November 7, 2006 at 12:21 pm
1) I created many data flow task packages.
2) Then a execute package task in which I ordered relationally execute above data flow packages.
3) What I find is when a package fails, it is rolled back, execution stops but not the packages before this failed one.
4) What to do to rollback all in case of error and commit only if all are successfully executed?
November 8, 2006 at 7:48 am
This appears to be one of those little tricks in SSIS where you have to use the right control flow item in order to accomplish this task.
The Sequence Container control flow is designed to group Data Flow and/or other control flow tasks into a unified entity. One can then set the properties (like Transactions) for that entity that will in turn affect each member within that container.
I'm more of a visual person than a text person, so here's some code that you can use to test this out to see if this accomplishes what you want.
I generated 2 tables, and then I used simple INSERT...VALUES statements via the Execute SQL Task to populate them. When I insert them into a Sequence Container, if one of the Execute SQL Tasks fails then the work for the other Execute SQL Task is rolled back.
--GENERATE TABLES
if
exists(select 1 from sys.tables where name='transactiontest1')
drop
table transactiontest1
go
create
table transactiontest1(
id
int not null,
username
varchar(15) null,
status
smallint)
go
if
exists(select 1 from sys.tables where name='transactiontest2')
drop
table transactiontest2
go
create
table transactiontest2(
id
int not null,
username
varchar(15) null,
status
smallint)
go
--GO TO BIDS AND DESIGN A NEW PKG
Select the Execute SQL Task and enter the following code in the SQL Statement box.
insert transactiontest1
values (1,'good to go',0)
Select a new Execute SQL Task and enter the following code in the SQL Statement box.
insert transactiontest2
values(2,'readyforme',0)
If you execute this package now then both components will run in parallel and neither will generate an error.
Go back to the package and change the code for TransactionTest2 so that it will fail by changing the value for the last column to a character. For example, use the following code:
insert transactiontest2
values(2,'readyforme','hh')
Now if you run the code, the first task will succeed and the table will be populated, but the second task will fail. Unfortunately, the failure of the 2nd task does not cause the values inserted by the 1st task to be rolled back.
Go back to the package and add a sequence container. Drag and drop both Execute SQL tasks into the container. Right click on the container, and select Properties. Go to the Transactions section and in the TransactionOption box, select "Required". Click ok and save.
Make sure that the DTC service for your Server has been started, then run the package.
Now, when the second task fails, the first task is automatically rolled back.
This is a very rudimentary use of the Sequence Container in order to implement transactions. I'm certain that this task can handle more complexity so I suggest reading up on it and the "Incorporating Transactions in Packages" topic in SSIS Books Online.
I hope that this points you in the direction that you were looking for.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply