SSIS custom logging to manage start point for Re-Runs packages

• ETLBatchController
• SQL

Introduction

A SQL Server Integration Services (SSIS) package can fail for many reasons. A data source might be offline, a server might go down, the network might be unavailable, or countless other problems might arise. Whatever the reason, the end result is the SSIS package stops running.

Background

Custom logging working process to manage start point for broken batch run on SSIS

Quick Example Idea

Have a master package which will execute several child packages. The master package has the following event-handlers - (OnInformation, OnError, OnWarning, OnPostExecute) each event handler have a custom code (.net custom task) to read the package name, task name and the message and insert into a custom log table for log information in SQL SERVER. A SQL Server Integration Services (SSIS) package can fail for many reasons. A data source might be offline, a server might go down, the network might be unavailable, or countless other problems might arise. Whatever the reason, the end result is the SSIS package stops running.

To make thing simple this article focuses on how to re-run at broken packages.

This might not be too big a deal; you can simply restart the package after the problem has been resolved and everything is back to normal. Event-handlers and Server login help you to identify problems but need a process which helps to identify failed batches to manage Start Points for Re-Runs.

One way to help protect against such a happening is to implement custom logging in SSIS package. To manage Package runs, Prefer to do it with a SQL Server Table. Keep a table in SQL Server with columns (package name, last executed, error, Intervals). Create a Master Package, which reads data from this table and execute package depending on their last executed and interval values. Also execute package which error out in their last execution so on and so forth, that’s the idea for custom logging to manage start point for Re-Runs.

Custom logging working process:

We are going to 

1. Create an SSIS application.
2. Prepare project connection string.
3. Set start and end package task parameters.
4. Prepare custom logging tables.
5. Prepare custom logging SP.
6. Test re-run for custom logging.

Create an SSIS application:

We create one SSIS project named “ETLBatchController” having two child packages(StgEmp.dtsx, StgModels.dtsx) and one master package(MBC.dtsx). It is a simple project child packages read data from staging to store in target tables and master package for the batch run.

Prepare project connection string:

To connect with SQL SERVER project need a connection string. With necessary credentials

Prepare a connection string.

Set start and end package task parameters:

For each package we set start and end task with defined set of parameters(package_name, current_extract_time, is_master_package, batch_number, source). Each parameter has its own necessity.

package_name => Used to get running package identity.

current_extract_time => used to get last pack execution time. Set a formula to get the package last execution time, Formula is in the sample code.

is_master_package => Use to defined is a master or child packages, for master package(MBC.dtsx) its value is one(1) and for child packages(StgEmp.dtsx, StgModels.dtsx) its value is zero(0). Detail information in the sample project.

batch_number => used to defined which batch is currently running.

Source => For which source run this batch or child package.

Prepare custom logging tables:

For custom logging, we create four tables (Batch_Event_Log, Batch_Statistics, Batch_Status, Package_Status )

Batch_Event_Log => log each package execution start and end activities.

Batch_Statistics => Log each package start and end execution time

Batch_Status => Log batch run condition is it successfully executed or failed.

Package_Status => Log each package execution start and end time with LSET(Last Execution Time) and CET(Current Execution Time)

Note: Sample project contains necessary scripts.

Prepare custom logging SP:

Each package Start Task and End Task Execute respectively SP usp_StartPackage and usp_EndPackage

Note: Sample project contains necessary scripts.

Test re-run for custom logging:

Batch run successfully

Batch run Failed

To make thing simple create a custom error on package StgModels.dtsx with named “Data Flow Error Task”. You get detail on a sample project. It's disabled default for failed case need to enable “Data Flow Error Task” and make to disable “Load Models”

Much More

• SQL Server Logging
• Custom Logging
• Checkpoints