Skip to content

Create SSIS Project Parameters from Biml

Biml (Business Intelligence Markup Language) - Project Parameters from BimlIf you are using BIDS Helper or BimlExpress to generate SSIS packages in the Project Deployment model, you have probably noticed that it is not possible to create project parameters from Biml. You can write Biml for the project and project parameters, but BIDS Helper / BimlExpress will only generate the SSIS packages for you and not the SSIS project parameters. The recommended solution is that you create the project parameters manually before you generate your SSIS packages from Biml.

(Want to know why? Read Scott Currie’s explanation in my blog post Why can’t I create SSIS Project Parameters from Biml?)

However, if you are a lazy developer like me, you probably don’t want to create and update project parameters manually. Perhaps you want to automatically create or update project parameters based on some metadata? You can do that!

Let’s take a look at a (semi-hardcoded, semi-hack) solution for creating SSIS project parameters from Biml in BIDS Helper / BimlExpress :)

Project.params

Luckily for us, the project parameters are specified in a separate file called Project.params in your project folder:
Project.params File
This is a very simple XML file that is easy to read and write. All we have to do is define our project parameters in Biml, specify where our Project.params file is located, and finally overwrite the Project.params file based on our Biml.

The Biml solution

The Biml solution is split in two Biml files to make it easy to use and maintain.
Download CreateSSISProjectParametersFromBimlInBIDSHelper.zip (Last updated on December 10th 2015)

In the first Biml file (01-CreateProjectParameters.biml), we specify the Parameters and the Annotation Tag ProjectParametersPath with the full path to our Project.params file:

In the second Biml file (02-AddProjectParameters.biml), we overwrite the Project.params file based on our Biml. This is intended to be a utility file that you won’t need to change, so I have minimized the code by default in this post. Click below to see the code:

The result

Add both Biml files to your SSIS project. Select both files, right-click and click Generate SSIS Packages. You will be prompted to reload the Project.params file that has been modified outside of the source editor. Click Yes / Yes to All, and you will see the new project parameters in your solution :)

Suggestions?

This is a semi-hardcoded, semi-hack solution for creating SSIS project parameters from Biml in BIDS Helper / BimlExpress. I’m sure it can be improved and I would love to hear your suggestions! Right now it gets the job done for me, and I hope you will also find it useful :)

Download CreateSSISProjectParametersFromBimlInBIDSHelper.zip (Last updated on December 10th 2015)

Who is Cathrine Wilhelmsen?

Cathrine is a Microsoft Data Platform MVP, BimlHero, Microsoft Certified Solutions Expert, author, speaker, blogger and chronic volunteer who loves teaching and sharing knowledge. She works as a consultant, architect and developer, focusing on Data Warehouse and Business Intelligence projects. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

Comments

Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Thanks!

Click to Show / Hide Comments