SSIS Framework package

A very interesting book on real case SSIS design solutions is  Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution Erik Veerman, Jessica M. Moss, Brian Knight, Jay Hackney

One of the first chapters is very interesting, the authors show how to develop a SSIS Framework capable to login the package execution at task level. In practice some tasks are executed on key events such as OnExecute, OnError ect to gather relevant info and save that error info in a support db

image

The second good advice is to use package configuration using environment variables, so the deployment on different machine would be hassles…

image

What I did is to add an extra task to clear the package logs and map a variable in the configuration to enable disable the task (through an expression)

You can find the solution with the SSIS packages here

and you can copy the template package in

C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

in this way when you add a new item in a SSIS project you can choose the new template with the template.

The extra code (compared to the code in the book) is about the logs table handling

Changing the value of the CleraTabLogs entry you can decide to reset or not the log tables

image

You can check that in the sql profiler if you like

image

I advice to read the book to understand the flow first.

ps

CRS is a project I am working and for which I created this code…sorry no time to celan up the names….

2 thoughts on “SSIS Framework package

  1. Pingback: Link Resource # 53 : May 08 – May 11 « Dactylonomy of Web Resource

  2. Pingback: Link Resource # 54 : May 11 – May 17 « Dactylonomy of Web Resource

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s