THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!
Note: Comments are moderated. Spam shall not pass! </GandalfVoice>

Designing an SSIS Framework

In preparation for SQL Saturday #84 – KalamazooSQLBits 9, and the PASS Summit 2011; I’ve been honing my presentation called Designing an SSIS Framework. I delivered this presentation a few times over the past couple months. Each time, I promised the attendees I’d send them the code if they emailed me… and a couple things got in the way:

First, a lot of people requested the presentation. Second, I got really busy! Both of those beat their respective alternatives.

I finished building and barely testing the code this evening. You can grab an updated copy here. The zip file contains the SSIS 2008 R2 solution and two other folders: Config and Sql. Config holds a configuration file that points to a database named SSISConfig on the default instance of your local workstation or server (edit SSISConfigParent.dtsConfig if you want to change this location).

To make it all work dynamically, you need to create a System Environment Variable named SSISConfig (case-sensitive) and set the value to the full path of SSISConfigParent.dtsConfig on your workstation or server. Next, run the “Create SSISConfig.sql” script in the Sql folder, followed by the “Add An SSIS Applications.sql” script (edited to reflect the folder that contains the SSIS packages on your workstation or server). This should get everything ready for the SSISConfig SSIS solution. When you execute Parent.dtsx, it should call Template1.dtsx, then Template2.dtsx, and finally ErrorTest.dtsx.

Let me know how it goes!

:{>

Published Friday, September 16, 2011 8:00 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Andy Vold said:

Thanks for posting this!  It is a great framework.

September 16, 2011 4:48 PM
 

Tim "the Trollman" said:

Thank you for sharing your packaging idea which is extremely valuable for those of us who need some guidance and example.

September 21, 2011 4:52 PM
 

Chris Dent said:

Thanks for the in depth session on Thursday @ SQL Bits (A Day of SSIS ) and the community day convering the Framework design above.

I have a small data merge project to do this week and will make an excellent start for the solution.

Many Thanks

October 3, 2011 11:53 AM
 

badrou zeggar said:

seems useful. i will give it a try.. thank you

October 10, 2011 1:03 PM
 

badrou zeggar said:

yes, tried it. it works well.

so i assume that we can viualize the errors and the the other events by querying the rables withing the database "SSISConfig".

PS: i am a beginner

October 11, 2011 9:59 AM
 

bwg said:

I'm seeing an error in the OnInformation event handler - it is firing prior to the PkgInstanceID being assigned.  I modified the LogEvent procedure to test if PkgInstanceID <> 0 prior to doing the insert.  This seems to have fixed the issue, though it throws away whatever the informational event was that was firing prior to PkgInstanceID being assigned.  

October 28, 2011 12:33 AM
 

andyleonard said:

Hi bwg,

  That sounds like a foreign key constraint firing. In most frameworks I deploy, a "zero state" is legitimate. It reflects an issue in the Parent package. In the past, I have solved this by allowing identity insert and inserting a record with ____ID == 0 into Application, Package, ApplicationInstance, and PackageInstance tables.

  It gets used whenever you need to log an Application-level fault - like "There are no packages for this application". Or if something tragic happens during an insert into one of the Instance tables and you get no returned ID value from the identity on that table.

  If it helps, think of the Parent package as the engine of a state machine.

Hope this helps,

Andy

October 28, 2011 10:11 AM
 

Lokesh Sharma said:

Hi Andy,

Can we download the reports also which you have shown in your demo

Regards

February 23, 2012 4:45 AM
 

andyleonard said:

Hi Lokesh,

  I need to publish the reports as well. Will get to that... eventually!

:{>

February 24, 2012 8:30 PM
 

Val said:

Hi Andy ,

Can you share pls the SSRS reports too ?

Regards,

Val

March 4, 2012 6:36 PM
 

andyleonard said:

Hi Val,

  I am working on the SSRS reports, I promise.

:{>

March 4, 2012 7:20 PM
 

Tasneem Syed said:

Hi Andy,

You have provided a very elegant solution for ssis_framework. Simplicity is what attracted me. I hope you will get the reports soon.

Thanks, Rgds

March 6, 2012 2:52 PM
 

Val said:

Hi Andy ,

You guys did a very good job !!!

Regards,

Val

March 20, 2012 9:37 AM
 

Val said:

Hi Andy ,

Do you have any example of the dynamic

cube  partitioning  using ssis  ?

Regards,

Val

March 21, 2012 1:05 PM
 

Chad said:

Andy,

I've downloaded the framework you've created.  I have all working except for the following error:

SQL Task: Executing the query "log.LogEvent" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_logSSISEvents_logPkgInstance_PkgInstanceID". The conflict occurred in database "SSISConfig", table "log.SSISPkgInstance", column 'PkgInstanceID'.

I'm evaluating frameworks for SSIS and want to know what this means in a bit more detail.  

Thanks,

Chad

March 29, 2012 8:20 PM
 

Eric said:

Hi Chad,

Andy addressed this issue above in response to BWG. It means the parent package OnInformation logging / insert violates a database referential integrity constraint. In this case the framework is trying to log the running Parent package implcity using the same rules as for a child package, but it isn't the same. The technical difference is the PkgInstanceID is not yet set (is 0) and a pack instance with a value of 0 is not yet logged in the context of this 'ETL batch run'.

I went a different fix route than Andy suggested above and replicated the Log Start of Child Package to the Pre-flight Ops section (renamed to Log Start of Parent Package), added a new variable for the parent path and default the PkgInstanceID to 0. This maintains the referential integrity of the logging event insert. I believe Andy is very happy for us to take his framework and enhance it for our own use as long as we leave intact his Copyright annotations.

We are retro fitting the framework to our existing batch runs. All child packages were separate steps in SQL Server Agent jobs each with its own dtsConfig file.

However, when you move your packages from DEV (eg. your own local setup)  to a system testing server, the child packages look for their dtsconfig files in the exact folder path you created them in on your local DEV setup.

I have tried numerous methods to fix this including

- add the child dtsconfig files into the config tab in SQL Server agent

- putting the child dtsconfig files in the same folder as the SSISConfig environment variable path

- putting the child dtsconfig files in the same folder as the child packages (the same as the PackageFolder value in the SSISConfig data table cfg.Packages)

All of the variables are connection strings (eg. server names, database names, file paths). Most of the recommendations state that best practice is to declare any global variables in the parent package and access them from the subordinate child package.

So the alternative is to re-engineer that child packages to do this. However, I would like to solve this challenge as the child package dtsconfig path has to be a stored value as at execute / failure time it is not simply defaulting to a location implicitly, it is explicitly looking for the file at the local DEV path.

Regards

Eric

April 2, 2012 7:22 AM
 

Eric said:

Following on from my earlier update, I exhausted every avenue I could find and came to the conclusion that we would need to re-engineer our child packages so that they pull all runtime variable values from the parent package.

All the reading I did, including MSDN resources. SQL Server Deep Dives Vol 2 stuff also by Andy and some detailed blogging by @JamieT, it is clear that this is the best practice and sorting this issue out now will pay dividends later.

And there is nothing like working through issues and decision points like this to thoroughly learn and buy into a framework architecture such as this. Thanks for being patient with all my questions Andy.

April 2, 2012 4:56 PM
 

Kevin said:

This solution works great if your child packages are on a file system.  However, if you would like to use the new Integration Services Catalog in 2012, I'm not sure how that would work.  Can you invoke a package dynamically stored in a catalog?  Obviously the connection manager method would have to change.  Any insight would help.  Thanks.

July 11, 2012 9:46 AM
 

andyleonard said:

Hi Kevin,

  Very true. I've done some work in the 2012 Catalog and intend to publish my findings soon. I like many aspects of the SSIS 2012 Catalog, but - in general - I find it an incomplete implementation. More later.

:{>

July 11, 2012 11:47 AM
 

Yadav said:

I have tested ...It works...nice work..!

Thanks a lot :-)

September 4, 2012 7:11 AM
 

Mary Ann said:

What does "cfg" schema stands for?

March 23, 2014 9:54 PM
 

andyleonard said:

Hi Mary Ann,

  Cfg stands for Config. This framework began its existence as a connection management solution for DTS.

:{>

March 24, 2014 9:24 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

My Company


Other Blog

Check out my personal blog...
http://andyleonard.me

Contact Me

Twitter: @AndyLeonard
Email: andy.leonard@gmail.com

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement