Update: If you are using the SSIS Catalog found in SSIS 2012 and later versions, you may be interested in the SSIS Framework Community Edition, an open source, Catalog-integrated execution framework.
In preparation for SQL Saturday #84 – Kalamazoo, SQLBits 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!
:{>
Learn more:
Recording: Introducing SSIS Framework Community Edition
Help!:
Enterprise Data & Analytics is here to help! Contact us today.
Thanks for posting this! It is a great framework.
Thank you for sharing your packaging idea which is extremely valuable for those of us who need some guidance and example.
Thank you for sharing your packaging idea which is extremely valuable for those of us who need some guidance and example.
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
seems useful. i will give it a try.. thank you
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
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.
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
Hi Andy,
Can we download the reports also which you have shown in your demo
Regards
Hi Lokesh,
I need to publish the reports as well. Will get to that… eventually!
:{>
Hi Andy ,
Can you share pls the SSRS reports too ?
Regards,
Val
Hi Val,
I am working on the SSRS reports, I promise.
:{>
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
Hi Andy ,
You guys did a very good job !!!
Regards,
Val
Hi Andy ,
Do you have any example of the dynamic
cube partitioning using ssis ?
Regards,
Val
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
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
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.
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.
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.
:{>
I have tested …It works…nice work..!
Thanks a lot 🙂
What does "cfg" schema stands for?
Hi Mary Ann,
Cfg stands for Config. This framework began its existence as a connection management solution for DTS.
:{>
Hi Andy,
So I have implemented it in SSIS 2012. I wanted to pass variables from parent package to child packages. Is this possible if the packages are in different projects?
Regards,
Mary Ann
Hi Andi,
Any news regarding the 2012 Catalog and the Framework ?
Regards Devy
Hi Devy,
SSIS 2012 (and 2014) supports Package Deployment Model which is backwards-compatible with previous versions of SSIS. As such, the file-system-based framework remains viable.
That said, Linchpin People markets an SSIS Framework that is integrated with the SSIS 2012/2014 Catalog. Ping me at andy.leonard@linchpinpeople.com for more information.
:{>
Hi –
I don’t seem to have a version of VS that will open or migrate the contents of the SSISConfigFrameworkAndReports_20120306 zip. Any chance of getting a solution that VS 2013 can open?
Thank you very much – Lisa
I am deploying this framework and I am running to the same error as above with the FK constraint issue. I would like to see any example of how you solve that issue. thanks in advance for your help.