May 23, 2012 at 11:59 am
I have an execute task that truncates the table when the package runs. Downstream I have dataflow tasks that fetches fresh data and populates that data using stored procs. Now I want to be able to rollback truncation if anything goes wrong anywhere downstream. how do i do this?
May 23, 2012 at 1:32 pm
Hi,
You can do the rollback in sql script or we can do in SSIS package
set the Transaction option property to required. and if you any sub tasks or packages set there property to Supported
if tasks are independent then set the property to not supported.
thanks.....
mamata
May 23, 2012 at 1:35 pm
http://www.codeproject.com/Articles/4451/SQL-Server-Transactions-and-Error-Handling
check out this link ....you can find the example of rollback stuff
May 23, 2012 at 1:53 pm
MisLead (5/23/2012)
I have an execute task that truncates the table when the package runs. Downstream I have dataflow tasks that fetches fresh data and populates that data using stored procs. Now I want to be able to rollback truncation if anything goes wrong anywhere downstream. how do i do this?
If using the built-in transactions in SSIS you'll be enlisting the help of the Windows Distributed Transaction Coordinator (DTC). Using transactions in SSIS across multiple servers as you are exploring is an advanced topic and requires some operating system configuration in addition to configuration of your SSIS package. I suggest you do some reading. Here are a couple articles to get you started:
How To Use Transactions in SQL Server Integration Services SSIS[/url]
Integration Services Transactions
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2012 at 3:44 am
opc.three (5/23/2012)
MisLead (5/23/2012)
I have an execute task that truncates the table when the package runs. Downstream I have dataflow tasks that fetches fresh data and populates that data using stored procs. Now I want to be able to rollback truncation if anything goes wrong anywhere downstream. how do i do this?If using the built-in transactions in SSIS you'll be enlisting the help of the Windows Distributed Transaction Coordinator (DTC). Using transactions in SSIS across multiple servers as you are exploring is an advanced topic and requires some operating system configuration in addition to configuration of your SSIS package. I suggest you do some reading. Here are a couple articles to get you started:
How To Use Transactions in SQL Server Integration Services SSIS[/url]
Indeed. An easier solution would be:
* an Execute SQL Task at the start with the command BEGIN TRAN.
* an Execute SQL Task at the end with the command COMMIT.
* set the property RetainSameConnection to true on the connection manager
(this is equivalent to doing the entire transaction in TSQL)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 24, 2012 at 10:01 am
Koen Verbeeck (5/24/2012)
opc.three (5/23/2012)
MisLead (5/23/2012)
I have an execute task that truncates the table when the package runs. Downstream I have dataflow tasks that fetches fresh data and populates that data using stored procs. Now I want to be able to rollback truncation if anything goes wrong anywhere downstream. how do i do this?If using the built-in transactions in SSIS you'll be enlisting the help of the Windows Distributed Transaction Coordinator (DTC). Using transactions in SSIS across multiple servers as you are exploring is an advanced topic and requires some operating system configuration in addition to configuration of your SSIS package. I suggest you do some reading. Here are a couple articles to get you started:
How To Use Transactions in SQL Server Integration Services SSIS[/url]
Indeed. An easier solution would be:
* an Execute SQL Task at the start with the command BEGIN TRAN.
* an Execute SQL Task at the end with the command COMMIT.
* set the property RetainSameConnection to true on the connection manager
(this is equivalent to doing the entire transaction in TSQL)
The two Execute SQL Tasks you mention point to the same database, using the same connection, sounds good. What happens to that transaction if an exception occurs while talking to the second instance on a different connection? I do not see a ROLLBACK. Are you relying on some implicit behavior of the SSIS connection manager in this technique?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2012 at 2:37 pm
opc.three (5/24/2012)
The two Execute SQL Tasks you mention point to the same database, using the same connection, sounds good. What happens to that transaction if an exception occurs while talking to the second instance on a different connection? I do not see a ROLLBACK. Are you relying on some implicit behavior of the SSIS connection manager in this technique?
The technique only works when using the same connection manager. So it will not work with tasks spread out over different instances (= different connection managers)
This method relies on the fact that if a package/tasks fails, the connection is stopped hence the pending transaction will be rolled back.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 24, 2012 at 3:16 pm
Koen Verbeeck (5/24/2012)
opc.three (5/24/2012)
The two Execute SQL Tasks you mention point to the same database, using the same connection, sounds good. What happens to that transaction if an exception occurs while talking to the second instance on a different connection? I do not see a ROLLBACK. Are you relying on some implicit behavior of the SSIS connection manager in this technique?The technique only works when using the same connection manager. So it will not work with tasks spread out over different instances (= different connection managers)
This method relies on the fact that if a package/tasks fails, the connection is stopped hence the pending transaction will be rolled back.
I may have misread the OPs requirements. My comment was assuming multiple instances were involved. That's not to say that using RetainSameConnection is not usable within a multiple-instance-scenario, but I would use the DTC for that kind of work. For all work on the same instance, RetainSameConnection is the simplest way to go, and what I usually use.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 25, 2012 at 12:08 pm
opc.three (5/24/2012)
Koen Verbeeck (5/24/2012)
opc.three (5/24/2012)
The two Execute SQL Tasks you mention point to the same database, using the same connection, sounds good. What happens to that transaction if an exception occurs while talking to the second instance on a different connection? I do not see a ROLLBACK. Are you relying on some implicit behavior of the SSIS connection manager in this technique?The technique only works when using the same connection manager. So it will not work with tasks spread out over different instances (= different connection managers)
This method relies on the fact that if a package/tasks fails, the connection is stopped hence the pending transaction will be rolled back.
I may have misread the OPs requirements. My comment was assuming multiple instances were involved. That's not to say that using RetainSameConnection is not usable within a multiple-instance-scenario, but I would use the DTC for that kind of work. For all work on the same instance, RetainSameConnection is the simplest way to go, and what I usually use.
Thanks a lot both. I think I can roll with the same connection manager here since i only have one sql connection string here. The other one is ADO.NET from remote, I don't think i would have control over that using Rollback in sql.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply