How to Setup Checkpoints in SSIS Package
Checkpoints in SSIS
Checkpoint configuration helps us to resume from the last task in the package. i.e., if the multiple tasks are there in a package. If there is any failure in any task it stores the fail task point in the checkpoint file once we restart the package the checkpoint helps to start from last point specified in checkpoint file. Once the package is succeeded it deletes the checkpoints.
1. Take two execute SQL Tasks, one task with correct SQL command and second task with improper command (so that it fails).
2. Go to each task → properties → fail package on failure: true.
3. Control flow → rt click → properties →
Checkpoint file name: desktop → check file.txt
Checkpoint usage: if exists
Save checkpoint: true.
4. Execute the package, as 2nd task is failed, checkpoint file is generated.
5. Rectify the SQL command in 2nd task instead of 1st task.
It uses various log providers to take the log information at particular “event”. The log providers are
- SSIS log provider for windows event log
- SSIS log provider for text files
- SSIS log provider for XML files
- SSIS log provider for SQL server
- SSIS log provider for SQL server profiler
In real time this log information is used to perform the below task.
- To eliminate bottle necks
- To trouble shoot the package
This log information is different from progress tab information because
i. It contains auditable information.
ii. Start point, end point of the tasks available in the package.
iii. Machine name, operator name etc.
SSIS menu → logging → select package (or) task in the left hand side panel →
Right hand side
Provider type: SQL server provider for text filesà
Implementing action at a particular event is called event handling.
Eg of events:
a) On post execute
b) On pre execute
c) On information
d) On error etc…..
Event handling Eg:
1) Sending an email after successful execution of package.
Go to event handler tab
Select Executable ⁄ Package Eventhandler ⁄ on post executes
Drag and drop send mail task, do the configuration.
Run package and observe the result
2) Delete the before loading the data into GRP LOAD table
Go to event handler tab
Executable ⁄ Data flow task Event handler ⁄ on press execute
Take execute SQL task and do the below settings
Sal statement: delete from GRP LOAD;
Execute package and see the expected result
Configuration → new connection → create file → specify file location → ok → ok
Details section → events → select on post execute → click ok → ok
These are helpful while migrating or moving the package from one environment to another environment.
Development to testing
Testing to production….etc.
There are many ways we create configuration.
1) XML configuration.
2) Windows registry entry variable
3) Environmental variable
4) Parent package variable
5) SQL server database.
Note: In real time the frequently used configuration are “XML and SQL server data base”.
As XML is industrial standard with platform independent, most of the organizations are perfecting it.
1. Take data flow task → flat file source (c:\Hyd.txt) → flat file destination (c:\Hyd-opt.txt)
2. SSIS menu → package configurations → check enable package configuration → click add → next.
Configuration type: XML configuration file.
Configuration filename: Browse and specify filename (New)
Click next → check the connection string property for source connection managers
Click next → finish → close
3. Go to configuration file → open using any editor like Microsoft visual studio version selector (or notepad or wordpad…etc).
Change source filename to c:\Banglore.Txt
Change destination filename to c:\Banglore.opt.txt
4. Run the package now the package runs with the configuration file settings
Enroll for Live Instructor Led MSBI Training