Posts Tagged ‘dynamic SQL’

Dynamically Generate SSIS Packages

June 17, 2012

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