Temp table and rollback in SSIS Package   

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.

date: Wed, 9 Jul 2008 02:56:03 -0700   author:   Ryo

RE: Temp table and rollback in SSIS Package   

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.

date: Wed, 9 Jul 2008 05:58:02 -0700   author:   Todd C

RE: Temp table and rollback in SSIS Package   

Thanks for your help.
I have already try this without success, I have an error that said begin 
statement of commit can't be found (I will try again to give you the exact 
message).
The other problem is that data in destination aren't roll backed with this 
method.

"Todd C" wrote:

> 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.

date: Wed, 9 Jul 2008 07:01:00 -0700   author:   Ryo