Blog

How to Setup Checkpoints in SSIS Package - MSBI

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.

Loading:

 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.

  1. To eliminate bottle necks
  2. 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.

Navigation:

SSIS menu → logging → select package (or) task in the left hand side panel →
Right hand side
Provider type: SQL server provider for text filesà

Event Handling

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.
Navigation:
         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
Navigation:
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


Package Configuration:

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.
 
XML configuration:

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
Save
4. Run the package now the package runs with the configuration file settings

           Enroll for Live Instructor Led MSBI TRAINING


RELATED COURSES

Get Updates on Tech posts, Interview & Certification questions and training schedules