Hello Friends I'm using three "ForEach Loop's" to load 3 different tables (OLEDB DEST) parallely, and my source is "Flat File" (.txt files).I'm using "File System Task" after "Data Flow Task" to archive flat files to Archive Folder.And i'm Truncating tables before "ForEach Loop" executes. Here I've a problem: If two files are loaded correctly with out any error then these two are archived.Coming to third file (with error in data), it'll not loaded in to dest. table (because its having a error) further it will not be archived.If i correct the data and trying to load, the 'Truncate' command will truncate all the data(1st,2nd,few data in 3rd file).Here the data is loading from 3rd flat file (because 1st & 2nd files are archived). Can any one please help me how should i write the restart logic(to load all the files) in this scenario. Thanks in advanve J S Reddy

The only way to provide restart functionality is to either delay archiving any of your files until all of them are loaded, OR Don't truncate the table before you load it, only delete the records associated with a particular file. I think you're going to choose the first option, because I don't think you know what records belong to particular files. In order to delay archiving files until all of them are processed completely, you have plenty of architectural choices. Here's one: Create a new folder to hold "files processed completely in this batch". Call this folder "Intermediate" or something like that. Before your original logic, use a Foreach Loop with a File System Task to move all files in the Intermediate folder back to your Source folder. Inside your original logic, when the files are processed properly, move them to the Intermediate folder, not the Archive folder. After your original logic, use a Foreach Loop with a File System Task to move all files in the Intermediate folder to the Archive folder. Talk to me now on

Try this: Put in two Execute SQL Taks and set them to BEGIN TRAN and COMMIT TRAN and connect them in the appropriate sequences in the Control Flow. You can even bracket the archiving of the files inside the transaction. There is no need for any ROLLBACK TRANS becuase it will rollback automatically on errer. Lastly, make sure they are both connected to the same Connection Manager, and for that CM, set its RetainSameConnection property to True. Another option would be to put in Row Counts in each Data Flow task that count rows REJECTED from a Destination. THen put in logic to only move the file (Precedence Constraint) if the Rows Rejected == 0. Hope this helps.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Thank you very much for you help Todd.Sorry for the late reply Regards J.S Reddy

Thank you very much for you help Todd.Sorry for late reply. Regards J.S Reddy

If a post solved your problem, please mark it as the answer. If you're still working on the problem, post back if you need clarification. Talk to me now on