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.
- Take two execute SQL Tasks, one task with correct SQL command and second task with improper command (so that it fails).
- Go to each task → properties → fail package on failure: true.
- Control flow → rt click → properties →
Checkpoint file name: desktop → check file.txt
Checkpoint usage: if exists
Save checkpoint: true.
- Execute the package, as 2nd task is failed, checkpoint file is generated.
- Rectify the SQL command in 2nd task instead of 1st task.
If you would like to Enrich your career with a MSBI certified professional, then visit Mindmajix - A Global online training platform: “MSBI Online Training” Course. This course will help you to achieve excellence in this domain.
It uses various log providers to take the log information at particular “event”. The SSIS 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
Subscribe to our youtube channel to get new updates..!
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
- It contains auditable information.
- Start point, end point of the tasks available in the package.
- 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:
- On post execute
- On pre execute
- On information
- 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
- Connection: DB-MSBI
- 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
------ Related Article: How to Create Table Report in Reporting Services - MSBI -------
Package Configuration 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:
- XML configuration.
- Windows registry entry variable
- Environmental variable
- Parent package variable
- 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.
- Take data flow task → flat file source (c:Hyd.txt) → flat file destination (c:Hyd-opt.txt)
- 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
- Go to configuration file → open using any editor like Microsoft visual studio version selector (or notepad or wordpad…etc).
(i)Change source filename to c:Banglore.Txt
(ii)Change destination filename to c:Banglore.opt.txt
- Run the package now the package runs with the configuration file settings
List of Related Microsoft Certification Courses:
|SCCM||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|