SSIS TRANSACTION ROLLBACK ON FILE TRANSFER FAILURE

Question

Is it possible to rollback SQL transactions that are part of an SSIS Package if the File transfer task part of the same package fails due to network or path not accessible issue?

I have a flat file, i read and process it and insert the data from that file into an table in my MS SQL DB. I then place the same file in an archive folder for reference purposes.

The package fails when the destination file path is not accessible due to network or permission issues. I fix this environment issue and re-run the package.

A problem creeps in as the records get duplicated as the earlier run has inserted the same records in the table.

Is it possible to rollback sql transactions if a non-sql task like file transfer fails?

Thanks Balaji.V

http://stackoverflow.com/questions/13626755/how-to-transaction-rollback-in-ssis — SenthilPrabhu, Jul 21 '14 at 04:21

score 0 · Accepted Answer · answered Jul 21 '14 at 06:52

0

You can use Sequence Containers to implement transactions. (Make sure that Microsoft Distributed Transaction Coordinator is turned on)

Here's a good article to get you going: Using SQL Containers in SSIS

answered Jul 21 '14 at 06:52

Jason Campbell

146
1
10

SSIS TRANSACTION ROLLBACK ON FILE TRANSFER FAILURE

1 Answers1