 | |
Temp table and rollback in SSIS Package
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Temp table and rollback in SSIS Package -
07-09-2008
, 04:56 AM
|
SQL Task 1|>Get the date (passed by user or current by default) and log the begin of the work __________|-->Destination connection +------------+ SQL Task 2|>Create and fill the #temp table __________|-->Source connection +------------+ DataFlow 1|>Copy data from first table __________|-->Both connection, DelayValidation=true, Use #temp, Rollback +------------+ DataFlow 2|>Copy data from second table __________|-->Both connection, DelayValidation=true, Use #temp, Rollback +------------+ SQL Task 3|>Delete data from second table __________|-->Source connection, DelayValidation=true, Use #temp, Rollback +------------+ SQL Task 4|>Delete data from first table __________|-->Source connection, DelayValidation=true, Use #temp, Rollback +------------+ SQL Task 5|>Log the end of the work __________|-->Destination connection |
#2
| |||
| |||
|
|
Hi, I have an SSIS package that use a #temp table to store the list of ID to compute in different DataFlows and SQL Tasks. This package has 2 connection managers (OLE DB), Source and Destination. In order to create the #temp table, RetainSameConnection is set to true on the Source connection and tasks that use #temp table have DelayValidation set to true. The problem is that with RetainSameConnection to true, TransactionOption must be the same in all the package and I want to rollback some step on package failure. Is that somebody knows how to do this? Here is the detail of the package: +------------+ |SQL Task 1|>Get the date (passed by user or current by default) and log the begin of the work |__________|-->Destination connection | +------------+ |SQL Task 2|>Create and fill the #temp table |__________|-->Source connection | +------------+ |DataFlow 1|>Copy data from first table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |DataFlow 2|>Copy data from second table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 3|>Delete data from second table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 4|>Delete data from first table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 5|>Log the end of the work |__________|-->Destination connection Regards. |
#3
| |||
| |||
|
|
Hi, I have an SSIS package that use a #temp table to store the list of ID to compute in different DataFlows and SQL Tasks. This package has 2 connection managers (OLE DB), Source and Destination. In order to create the #temp table, RetainSameConnection is set to true on the Source connection and tasks that use #temp table have DelayValidation set to true. The problem is that with RetainSameConnection to true, TransactionOption must be the same in all the package and I want to rollback some step on package failure. Is that somebody knows how to do this? Here is the detail of the package: +------------+ |SQL Task 1|>Get the date (passed by user or current by default) and log the begin of the work |__________|-->Destination connection | +------------+ |SQL Task 2|>Create and fill the #temp table |__________|-->Source connection | +------------+ |DataFlow 1|>Copy data from first table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |DataFlow 2|>Copy data from second table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 3|>Delete data from second table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 4|>Delete data from first table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 5|>Log the end of the work |__________|-->Destination connection Regards. |
#4
| |||
| |||
|
|
Hi, I have an SSIS package that use a #temp table to store the list of ID to compute in different DataFlows and SQL Tasks. This package has 2 connection managers (OLE DB), Source and Destination. In order to create the #temp table, RetainSameConnection is set to true on the Source connection and tasks that use #temp table have DelayValidation set to true. The problem is that with RetainSameConnection to true, TransactionOption must be the same in all the package and I want to rollback some step on package failure. Is that somebody knows how to do this? Here is the detail of the package: +------------+ |SQL Task 1|>Get the date (passed by user or current by default) and log the begin of the work |__________|-->Destination connection | +------------+ |SQL Task 2|>Create and fill the #temp table |__________|-->Source connection | +------------+ |DataFlow 1|>Copy data from first table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |DataFlow 2|>Copy data from second table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 3|>Delete data from second table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 4|>Delete data from first table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 5|>Log the end of the work |__________|-->Destination connection Regards. |
#5
| |||
| |||
|
|
Hi, I have an SSIS package that use a #temp table to store the list of ID to compute in different DataFlows and SQL Tasks. This package has 2 connection managers (OLE DB), Source and Destination. In order to create the #temp table, RetainSameConnection is set to true on the Source connection and tasks that use #temp table have DelayValidation set to true. The problem is that with RetainSameConnection to true, TransactionOption must be the same in all the package and I want to rollback some step on package failure. Is that somebody knows how to do this? Here is the detail of the package: +------------+ |SQL Task 1|>Get the date (passed by user or current by default) and log the begin of the work |__________|-->Destination connection | +------------+ |SQL Task 2|>Create and fill the #temp table |__________|-->Source connection | +------------+ |DataFlow 1|>Copy data from first table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |DataFlow 2|>Copy data from second table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 3|>Delete data from second table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 4|>Delete data from first table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 5|>Log the end of the work |__________|-->Destination connection Regards. |
#6
| |||
| |||
|
|
Hi, I have an SSIS package that use a #temp table to store the list of ID to compute in different DataFlows and SQL Tasks. This package has 2 connection managers (OLE DB), Source and Destination. In order to create the #temp table, RetainSameConnection is set to true on the Source connection and tasks that use #temp table have DelayValidation set to true. The problem is that with RetainSameConnection to true, TransactionOption must be the same in all the package and I want to rollback some step on package failure. Is that somebody knows how to do this? Here is the detail of the package: +------------+ |SQL Task 1|>Get the date (passed by user or current by default) and log the begin of the work |__________|-->Destination connection | +------------+ |SQL Task 2|>Create and fill the #temp table |__________|-->Source connection | +------------+ |DataFlow 1|>Copy data from first table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |DataFlow 2|>Copy data from second table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 3|>Delete data from second table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 4|>Delete data from first table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 5|>Log the end of the work |__________|-->Destination connection Regards. |
#7
| |||
| |||
|
|
Hi, I have an SSIS package that use a #temp table to store the list of ID to compute in different DataFlows and SQL Tasks. This package has 2 connection managers (OLE DB), Source and Destination. In order to create the #temp table, RetainSameConnection is set to true on the Source connection and tasks that use #temp table have DelayValidation set to true. The problem is that with RetainSameConnection to true, TransactionOption must be the same in all the package and I want to rollback some step on package failure. Is that somebody knows how to do this? Here is the detail of the package: +------------+ |SQL Task 1|>Get the date (passed by user or current by default) and log the begin of the work |__________|-->Destination connection | +------------+ |SQL Task 2|>Create and fill the #temp table |__________|-->Source connection | +------------+ |DataFlow 1|>Copy data from first table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |DataFlow 2|>Copy data from second table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 3|>Delete data from second table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 4|>Delete data from first table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 5|>Log the end of the work |__________|-->Destination connection Regards. |
#8
| |||
| |||
|
|
Hi, I have an SSIS package that use a #temp table to store the list of ID to compute in different DataFlows and SQL Tasks. This package has 2 connection managers (OLE DB), Source and Destination. In order to create the #temp table, RetainSameConnection is set to true on the Source connection and tasks that use #temp table have DelayValidation set to true. The problem is that with RetainSameConnection to true, TransactionOption must be the same in all the package and I want to rollback some step on package failure. Is that somebody knows how to do this? Here is the detail of the package: +------------+ |SQL Task 1|>Get the date (passed by user or current by default) and log the begin of the work |__________|-->Destination connection | +------------+ |SQL Task 2|>Create and fill the #temp table |__________|-->Source connection | +------------+ |DataFlow 1|>Copy data from first table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |DataFlow 2|>Copy data from second table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 3|>Delete data from second table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 4|>Delete data from first table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 5|>Log the end of the work |__________|-->Destination connection Regards. |
#9
| |||
| |||
|
|
Hi, I have an SSIS package that use a #temp table to store the list of ID to compute in different DataFlows and SQL Tasks. This package has 2 connection managers (OLE DB), Source and Destination. In order to create the #temp table, RetainSameConnection is set to true on the Source connection and tasks that use #temp table have DelayValidation set to true. The problem is that with RetainSameConnection to true, TransactionOption must be the same in all the package and I want to rollback some step on package failure. Is that somebody knows how to do this? Here is the detail of the package: +------------+ |SQL Task 1|>Get the date (passed by user or current by default) and log the begin of the work |__________|-->Destination connection | +------------+ |SQL Task 2|>Create and fill the #temp table |__________|-->Source connection | +------------+ |DataFlow 1|>Copy data from first table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |DataFlow 2|>Copy data from second table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 3|>Delete data from second table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 4|>Delete data from first table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 5|>Log the end of the work |__________|-->Destination connection Regards. |
#10
| |||
| |||
|
|
Just curious: If you were to run this using the #temp table but were NOT concerned about the transactions stuff, does it still run? I thought that SSIS had some issues with #temp tables but have never tried using them myself. You might try adding two Execute SQL Tasks before and after the entire set and set the T-SQL to "BEGIN TRAN" and "COMMIT TRAN" (make sure they are both connected to the same source, and that the CM is set to RetainSameConnection=True). No need for a ROLLBACK becuase it will happen automaticaly on failure. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] "Ryo" wrote: Hi, I have an SSIS package that use a #temp table to store the list of ID to compute in different DataFlows and SQL Tasks. This package has 2 connection managers (OLE DB), Source and Destination. In order to create the #temp table, RetainSameConnection is set to true on the Source connection and tasks that use #temp table have DelayValidation set to true. The problem is that with RetainSameConnection to true, TransactionOption must be the same in all the package and I want to rollback some step on package failure. Is that somebody knows how to do this? Here is the detail of the package: +------------+ |SQL Task 1|>Get the date (passed by user or current by default) and log the begin of the work |__________|-->Destination connection | +------------+ |SQL Task 2|>Create and fill the #temp table |__________|-->Source connection | +------------+ |DataFlow 1|>Copy data from first table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |DataFlow 2|>Copy data from second table |__________|-->Both connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 3|>Delete data from second table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 4|>Delete data from first table |__________|-->Source connection, DelayValidation=true, Use #temp, Rollback | +------------+ |SQL Task 5|>Log the end of the work |__________|-->Destination connection Regards. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.