Mindmajix

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.

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


 

0 Responses on How to Setup Checkpoints in SSIS Package"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.