Protean

After some prompting, I decided it was about time I posted the solution to my initial post on the subject of metadata driven SSIS.

If you have read part one of this series (or other posts related to replication and sql agent), it will come as no surprise to you to know that I am a big proponent of metadata driven control in SQL Server. Whilst more complex to code, it is infinately easier to manage across environments.

I will add further explanations to this package at a later date, but for now, here is the solution and a very brief summary of what it does.

It has 3 elements.

1. There is a control database (which needs to be configured in the parent package), having 5 tables. 4 hold metadata related to the child packages and the final one manages the process flow, and as a by product allows for monitoring of the ETL status. There are also 3 stored procedures which load the queue, update the queue and check for dependencies.

• dbo.Package
• dbo.PackageJobs
• dbo.PackageDependency
• dbo.Connections
• dbo.PackageQueue

2. There is an SSIS parent package. The logic in the package is summarized below and simplified somewhat. The best place to get a better understanding is the parent package itself.

1. Read the PackageQueue
2. Do I have any records in the queue that indicate there are packages to run?
3. If so, does it have any dependencies? If it does move on to the next and come back to it later, if not execute the child package.
4. Have there been any errors. Does the metadata say I should fail the whole batch or should I just fail the package and move on to the next?

3. And finally there is a simple web page. I haven’t got the code to hand on my laptop here in Buenos Aires, however if you wanted to code your own, it is a simple data driven web page that checks the run code in the dbo.PackageQueue table and based on the result, displays the status of all the packages assigning a colour to the record depending on whether its currently running, waiting or failed.

Download the zip file here.

I apologise in advance for the very sparce explanation given, but I promise I will update the post with more detail in the coming weeks. Please leave a comment or send me an email if there is anything specific you want to ask.

Hope you had a good holiday

Frank

This entry was posted on Thursday, December 27th, 2007 at 2:23 am and is filed under Nuggets, SSIS, SQL Server 2005, Datawarehouse, Methodology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.