Dynamically Generate SSIS Packages

The the following video describes the basics of how to generate SSIS packages from metadata. This technique uses Dynamic SQL and BiML from BIDS Helper to generate the SSIS packages.

Code from video

While the above video demonstrates a very simple example, the same technique can be used to generate more complex SSIS packages. One of the challenges to applying this technique to more complex scenarios, is that it can be difficult to create the initial BiML for the template SSIS package. In my example from the video the template SSIS package simply queries the data source and loads the data into a multi-cast.

Varigence is the company that created BiML and they have a very useful tool called Mist which has a lot of interesting  features.  One of the most important features is a the ability to convert an existing SSIS package to BiML.

Mist will enable to you generate SSIS packages dynamically without having  to know BiML:

  1. Create Template SSIS Package in BIDS/SSDT
  2. Use Mist to convert Template SSIS Package to BiML
  3. Decide which sections of the BiML you want make dynamic
  4. Build Dynamic SQL Statement using the BiML from the second step

Conclusion:

Being able to dynamically generate SSIS packages can greatly reduce SSIS package development time and administrative effort. Additionally, you  enforce standards across all of the packages in your project such standardizing SSIS execution trees and task names .

Advertisements

Tags: ,

3 Responses to “Dynamically Generate SSIS Packages”

  1. Paul Says:

    Great post. You might check out LeapFrogBI.com as well. This SaaS tool create SSIS packages and is built specfically for data mart development.

  2. Strategies and Languages for Generating Biml | Made2Mentor Says:

    […] Another friend of mine, Garrett Edmondson (Blog), has a video on his blog where he uses T-SQL string concatenation to generate simple SSIS Packages. […]

  3. Aash Says:

    A very good explanation and a very productive way of generating lots of packages.

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


%d bloggers like this: