I am trying to implement the check pointing by creating a separate table which will monitor all the pkg related information like PkgStatus =Successful/Error and Flag Column = 0/1 As the pkgs run sucessfully each pkg detail gets registered in a monitoring table, there is a flag column which has 0 or 1 (1 means ran successfully and 0 means failed), here if we want to re run the pkg after successful run we have to manually update the monitoring table and change the flag from 1 to 0 and start the pkg from the master pkg. So if the status is Error or FlagStatus =0 then re run the pkg, or Status is Successful or FlagStatus = 0 then rerun the pkg(re running the pkg even though the pkg has run succefully by manually updating the FlagStatus to 0 from 1). Now all this works but when i run the pkg first time it looks for the Status / FlagStatus values which will not be there as this is the first run for the pkg, so how to implement this scenario for the first run ? waiting for a quick reply ThanksAnky

First of all, why are you 'rolling your own' CheckPoints when SSIS has built-in functinality for this? Why re-invent the wheel? Second, you could put in a step at the start of the package that queries the table, and if ALL rows' status flags are 1 (meaning that EVERYTHING ran sucessfull to completion on the last run), then UPDATE all the flags to 0. Simple. But I would rather go with canned functionality. It works great for me.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

If you have dtsx pkgs names passed as parameter, inbuilt checkpoint doesnt work as at first run it saves the parameter passed and when we re run the pkgs from point of failure , checkpoint xml files saves the old dtsx file name as value in the memory which doesnt get updated at re run and it fails the pkg again. after modifying the pkg we have to modify the checkpoint xml as well to make it work, which is not feasible So i came up with my own check pointing scenario. Inbuilt chekpointing works only with the data issues not with parameter related issue, if you still have a feasible logic pls let me know thanks. Anky

What checks for the statuses? I am thinking of creating a third value, say '2' for the purpose of indicating the situation when a package is new and awaits execution the first time.Arthur My Blog

You also started this thread http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/1ef1f6ba-45c8-49e2-8882-04da20b5954c about the fact that the dtsx package name variable that you assign with a /SET command gest persisted through the CheckPoint file. The basic response is this: Your package would need to have a way to PULL the dtsx File Name at a point that gets executed AFTER you re-start it from a CheckPoint. you would need a good series of Sequence Containters with the proper properties set in order to make it happen, but it CAN be done. Consider a single Sequence container with the following tasks inside it: 1) An Execute SQL Task that queries the 'control' table to get the name of the dtsx package file to run. This value gets stored in a parameter. 2) The Execute Package task that uses the dtsx file name from above and executes it. Now, if Step 2 fails, get it to ALSO fail the Sequence Container so that upon re-start from a CheckPoint, (Assuming, of course, that you have modified the Control table and corrected the dtsx file name) Step 1 will RUN AGAIN and get the CORRECTED file name. There, you now have Built-in CheckPoints with the ability to correct and re-start after failure WITHOUT needing to edit the CheckPoint file.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.