Insert in Dataflow Task Not Rolling Back

in sequence container, have populate county2 dataflow task, and a delete staging execute sql task. in dataflow task, single table called 'county2' populated 'county2_staging'. these tables in sql server 2005 database runs on 64 bit edition.

 

after populate county2 dataflow task completes successfully, records in staging table deleted county2_staging in delete staging sql task.

 

before populate county2 dataflow task, have execute sql task starts transaction:

begin transaction

 

after populate county2 dataflow task , delete staging execute sql task, have execute sql task commits transaction:

 

commit transaction

 

if error occurs within sequence container, sequence container failed. have execute sql task in ontaskfailed event of sequence container.

 

the problem having when sequence container fails, delete staging execute sql task rolled back, insert occurs between county2_staging , county2 in populate county2 dataflow task not.

 

why won't insert in dataflow task roll back? retainsameconnection property of connection manager (ole db sql server) set true'.

 

thank help!

 

cdun2

it looks using same connection manager (retainsameconnection = true) between first execute sql task in sequence container (the 1 issues begin transaction) , destination in dataflow, while using different connection manager in dataflow source (points same database) does trick.

 

cdun2

 

SQL Server  >  SQL Server Integration Services