Dynamic SSIS Packages, Part 4.

In the first three parts of the series, we looked at setup for dynamic packages. It is very important that you read these first before attempting to download and run this package…it won’t work for you until you have an understanding of this concept, and have the prerequisites already installed.

In this post, I attach the .dtsx file for you to download and run, along with a short video to "demo" the functionality. As mentioned, suppose that you had 2000 files to import. You certainly would not want to write 2000 SSIS packages; you wouldn't want to rewrite those same packages somewhere down the road when Microsoft decides make a slight change to SSIS, either. With this package that I have here, you write only one to import, and one to export - parameters that you set up prior to executing the package directs the generic package on the fly. Simply create the drive tables with data to feed the parameters, and the package creates the sources, pipelines, and destinations in memory.

To gain this type of functionality, we leverage the powerful SSIS API. As a reference, check out Building Packages Programmatically on the Microsoft site. While they don't show you how to do exactly what we do here, you can see how this package was created by combining all of the information from their pages.

For watching the video below, I wanted to keep the screen resolution at full, so make sure and use full screen to view. You can also flip to HD for a clearer image. Now that I try it though, I see that it doesn’t expand to full screen here in my blog, so go ahead and just click through the image below and watch it on YouTube (you can right-click it as well and choose “Watch on Youtube”). Make sure and go to 720 HD and full screen for best viewing.  Maybe someday I’ll have a good screen capture program for videos like all of the SQL Server MVPs and gurus have, but right now I have to use the freebies.

Pretty sweet, eh? I believe that this is the slickest SSIS package that I’ve ever seen, certainly, and I hope that you like it as well. I’ve seen blogs and articles on the subject of ‘dynamic’ packages, but maybe not one to this extent.

I am planning the following posts as follow-ups to this series:

• Import Package
• Combining both packages
• Dissecting the SSIS API and the code for each dynamic SSIS package
• Expanding the scope of these packages (variables, logging, debugging, error handling)
• Others

Give me a shout if you have problems running this package, and again, if you have not read the first three parts, please do so before trying to wire this package up.

Thanks for visiting,

Lee

---------------------------

Edit: 8/10/2011 – I’ve added the database scripts and zipped up the SSIS package here.

Scripts and SSIS package