You can use the Transact SQL transactions to accomplish the result. I want to empty and refill a table with values from a CSV file, but I want to keep the old data when the refill fails. My package:
1) Container
Add a Sequence Container and drag the existing Execute SQL Task (which empties the table) and the Data Flow Task (which fills the table) to it.
2) Start Transaction
Add an Execute SQL Task before the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: BEGIN TRAN MyTran.
3) Commit Transaction
Add an Execute SQL Task after the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: COMMIT TRAN MyTran.
4) Rollback Transaction
Add an other Execute SQL Task after the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: ROLLBACK TRAN MyTran.
5) Precedence Contraint
Change the Value property of the Precedence Contraint between the Sequence Container and the Rollback from Success to Failure. If something fails in the Sequence Container the Rollback command will be executed.
6) RetainSameConnection
Now the most important thing. Change the RetainSameConnection property of the database connection from false to True.
7) The Result
That's all there is. Now you can test your package. You can open the CSV file in Excel to lock the file and fail the package.
Source: http://microsoft-ssis.blogspot.in/2011/09/ssis-transactions-with-tsql.html
 |
| Example |
1) Container
Add a Sequence Container and drag the existing Execute SQL Task (which empties the table) and the Data Flow Task (which fills the table) to it.
 |
| Sequence Container |
2) Start Transaction
Add an Execute SQL Task before the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: BEGIN TRAN MyTran.
 |
| Start Transaction |
 |
| BEGIN TRAN |
3) Commit Transaction
Add an Execute SQL Task after the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: COMMIT TRAN MyTran.
 |
| Commit Transaction |
 |
| COMMIT TRAN |
4) Rollback Transaction
Add an other Execute SQL Task after the Sequence Container and use the same connection as in the other tasks. Enter the following statement in the SQLStatement field: ROLLBACK TRAN MyTran.
 |
| Rollback Transaction |
 |
| ROLLBACK TRAN |
5) Precedence Contraint
Change the Value property of the Precedence Contraint between the Sequence Container and the Rollback from Success to Failure. If something fails in the Sequence Container the Rollback command will be executed.
 |
| Precedence Contraint |
 |
| Failure |
6) RetainSameConnection
Now the most important thing. Change the RetainSameConnection property of the database connection from false to True.
 |
| RetainSameConnection |
7) The Result
That's all there is. Now you can test your package. You can open the CSV file in Excel to lock the file and fail the package.
 |
| The Result |
Source: http://microsoft-ssis.blogspot.in/2011/09/ssis-transactions-with-tsql.html
No comments:
Post a Comment