This should create a rudimentary source table which will contain 6 records and a destination table which we will be loading the source data into. Next, let\u2019s create a simple SSIS package. When you finished lying out and joining all the components together you should be able to see a structure which resembles the image below.<\/p>\n
To step through the individual components, first, place a \u2018Date Flow Task\u2019 on the Control Flow and going into \u2018Data Flow\u2019 tab place \u2018OLE DB Source\u2019 and \u2018OLE DB Destination\u2019, link them together and establish the mapping between the columns from Tbl_Source to Tbl_Destination. Go back to the \u2018Control Flow\u2019 and place another \u2018Execute SQL Task\u2019, call it \u2018SQL Task \u2013 Raise Error\u2019. Then create a connection to the database and write the following statement in the \u2018SQLStatement\u2019 property.<\/p>\n The purpose of this is for the package to fail and return an error on runtime. Once more, add \u2018Execute SQL Task\u2019, call it \u2018SQL Task \u2013 Update Age Category\u2019 and input the following query which will be responsible for updates to the \u2018Age Category\u2019 column in the destination table.<\/p>\n At this point all we have got is just a simple package which, when run, will fail on step two (division by zero). As the purpose of this exercise is to implement restartability, we need to enable package checkpoint functionality so the next time the package is run, it will start from the step which has failed and hasn\u2019t been executed, rather than from the very start. This will prevent the data from the destination table to be transferred twice for the exactly the same records and prevent duplication and issues with further processing and output. To enable checkpoints in our package, thus creating a point of failure where the package will restart from, click on an empty area in the \u2018Control Flow\u2019 and go to \u2018Properties\u2019 window. Next, in the \u2018Checkpoints\u2019 category set \u2018SaveCheckpoints\u2019 as \u2018True\u2019, \u2018CheckpointUsage\u2019 as \u2018IfExists\u2019 and set \u2018CheckpointFileName\u2019 to a file with any name, ending in .XML. For the purpose of this post, it is probably best to save it in your local c:\\ drive for convenience but in a production environment you will need to have a designated folder for this purpose. Finally, change the \u2018FailPackageOnFailure\u2019 property to \u2018True\u2019 on all \u2018Control Flow\u2019 tasks as per below.<\/p>\n It is time to run the package for the first time now and during its first run you should get an error half way through the execution.<\/p>\n This is expected as our SQL statement \u2018SELECT 1\/0\u2019 should throw an exception. Also, our destination table should be incomplete as the UPDATE Statement has not been yet executed and all the \u2018Age_Category\u2019 fields should be unpopulated (NULL values). Let\u2019s change this SQL code in ‘SQL Task – Raise Error’ component into the following statement in order to rectify this error.<\/p>\n Now, let\u2019s run the package again. This time around you should notice that instead of running from the beginning, the package should start from step 2 i.e. \u2018SQL Task \u2013 Raise Error\u2019 and finish successfully.<\/p>\n![\"\"](\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2012\/08\/SSIS_2012_Pckg_CheckPoints_ControlFlow.png\" "\\"SSIS_2012_Pckg_CheckPoints_ControlFlow\\"")
<\/a><\/p>\n\r\n--DUMMY SELECT TO THROW AN ERROR\r\nSELECT 1\/0\r\n<\/pre>\n
\r\n--UPDATE tbl_Destination WITH Age_Category\r\nUPDATE Tbl_Destination\r\nSET Age_Category = CASE WHEN Age < 18 THEN 'Juvenile' \r\n WHEN Age >=18 AND Age < 50 THEN 'Middle Age' \r\n WHEN Age > 50 THEN 'Senior' END\r\n<\/pre>\n
<\/a><\/p>\n<\/a><\/p>\n\r\n--CORRECT SELECT STATEMENT TO AVOID ERROR\r\nSELECT 1\/1\r\n<\/pre>\n