<div>
In the preparation step, I set it to &quot;DELETE FROM TABLE X&quot; instead of truncate.<br />
<br />
I put a Sequence Container around the Control flow and set it to Transaction Required.<br />
Thus, does this now mean that if the DataFlow Task fails, then the Transaction will roll back and undo the Preparation SQL task?
</div>


In the preparation step, I set it to "DELETE FROM TABLE X" instead of truncate.

I put a Sequence Container around the Control flow and set it to Transaction Required.
Thus, does this now mean that if the DataFlow Task fails, then the Transaction will roll back and undo the Preparation SQL task?

<div>
Yes, from what you wrtite it looks right. But please do the test example first before going to destroy production data :) :)
</div>


Yes, from what you wrtite it looks right. But please do the test example first before going to destroy production data :) :)

<div>
<div class="content wysiwyg-content">
I am new to integration services (sql server 2005) and am trying to set up a DTS package (SSIS Package).<br />
<br />
In old sql, I would make a package. &nbsp; As part of the package I would make it a transaction and roll back on failure.<br />
<br />
I have a oracle database and a SQL database. &nbsp;I want to import from oracle.table1 into sql.table1, replacing the rows in sql.table1 with those from oracle. &nbsp;Using the wizard, it provides a turncate statement followed by a dataflow of import.<br />
<br />
However, I need it to roll back to pre-turncate if the import fails. &nbsp;What step am i missing?
</div>
</div>


I am new to integration services (sql server 2005) and am trying to set up a DTS package (SSIS Package).

In old sql, I would make a package.   As part of the package I would make it a transaction and roll back on failure.

I have a oracle database and a SQL database.  I want to import from oracle.table1 into sql.table1, replacing the rows in sql.table1 with those from oracle.  Using the wizard, it provides a turncate statement followed by a dataflow of import.

However, I need it to roll back to pre-turncate if the import fails.  What step am i missing?

Integration Services Roll back on Failure

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.