Checkpoints in SSIS

Checkpoints in SSIS Package 

Checkpoint configuration helps us to resume 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 the last point specified in the checkpoint file. Once the package is succeeded it deletes the checkpoints.

• Take two execute SQL Tasks, one task with correct SQL command and the 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, the checkpoint file is generated.
• Rectify the SQL command in the 2nd task instead of the 1st task.

If you would like to Enrich your career with a  certified professional, then visit Mindmajix - A Global online training platform: “Checkpoint Certification Training” Course. This course will help you to achieve excellence in this domain.

Loading:

 It uses various log providers to take the log information at a 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

• SSIS log provider for SQL server profiler

In real-time, this log information is used to perform the below task.

• To eliminate bottlenecks
• To troubleshoot the package

This log information is different from progress tab information because

• It contains auditable information.
• Start point, the endpoint of the tasks available in the package.
• The 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à

Frequently Asked CheckPoint Interview Questions & Answers

Event Handling

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 the package.

Navigation:

         Go to the event handler tab

                    ↓

Select      Executable  ⁄ Package                     Eventhandler  ⁄ on post executes

Drag and drop send mail task, do the configuration.

Run the package and observe the result

2) Delete the before loading the data into the GRP LOAD table

Navigation:

• Go to the 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:

Package Configuration is 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 is “XML and SQL server database”. As XML is an industrial standard with platform-independent, most organizations are perfecting it.

XML configuration:

• Take data flow task → flat file source (c:Hyd.txt) → flat file destination (c:Hyd-opt.txt)
• SSIS menu → package configurations → check to enable package configuration → click to add → next.
                    Configuration type: XML configuration file.
                    Configuration filename: Browse and specify a filename (New)
                    Click next to → check the connection string property for source connection managers
                    Click next to → 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
  (iii)Save
• Run the package now the package runs with the configuration file settings       

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator