How to Rollback Transactions

hi everybody,

i have 4 flat files from a source folder which updates four different tables, this has to be done parallely,on success of this transaction the files have to be moved to another folder.

my problem comes here,now if there is any problem in moving any file to another folder,that particular transaction has to be rolledback without affecting others.i tried setting the transaction property of the control flow,but it rollbacks all the transaction..

please help me on this

You can scope transactions to a container, not just a package. Use Sequence containers to give some separation between the four load processes and their file operations.

You could also use the manual method of managing transactions, (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/SSIS-Nugget_3A00_-RetainSameConnection-property-of-the-OLE-DB-Connection-Manager.aspx), just repeat the pattern four times, once for each load, with four separate connections, one for each load, and the associate SQL tasks used to manage thetransaction.

|||

hi darren,

thanks for your post it was very helpful.

i have used foreach containers for the four load process as i can get multiple files.

now i have one more problem,my input file names will have format like this

yyyymmdd_salesdataforproduct_yyyymmdd_hhmmss.txt

here the first date is bussiness date and the second one is the sysytem date..if i get multiple file i have to proceess considering the second date.but by default the foreach loop is considering the first date,what can i do to ensure that only second is used to process my files.

thanks in advance

srikanth

|||

You could make the foreach loop to go through all files and then use expressions in the precedence constraints to decide whether a file needs to be processed or not. You may need aditional variables to get the dates and compare it. A simplier example here:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html