Create SSIS Project Parameters from Biml

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 SQL Server Data Warehouse architect and Business Intelligence developer. She organizes, volunteers and speaks at SQLSaturdays, and is a proud Microsoft Data Platform MVP and BimlHero Certified Expert. She loves sci-fi, fantasy, coffee, chocolate and smilies :)

14 thoughts on “Create SSIS Project Parameters from Biml”

Works like a charm, Cathrine, thanks a lot for this workaround!
Keep up the good work :-)

Thank you, very happy to hear that! :)

Your solution works very well, thank you for making it public!

I’m just starting with BIML and project-level parameters were the first obstacle. Surprised to see that BIDS Helper doesn’t support them out of box when they are such fundamental parts of functionality from 2012.

Hi Istvan, you can read Scott Currie’s explanation over here :) https://www.cathrinewilhelmsen.net/2016/05/27/cant-create-ssis-project-parameters-from-biml/

Cathrine – Very nice article. I have used your solution. Can we restricted the length of parameters? I just want to use a project parameter with length 8. Will it be possible? I have a very tricky problem.

Hi Manish, you can’t restrict the length of parameters in SSIS, so you can’t do that with Biml either.

Is this issue solved with using bimlexpress?

No, and it will not be solved in either BIDS Helper or BimlExpress unless Microsoft changes the way parameters are stored. Long story short (and I am currently writing a new blog post about this): it’s not possible to separate parameters that have been added manually and parameters that have been added via Biml. The only option is to overwrite the entire .params file and all the parameters. This could be an option for those who are using Biml only, but it is too risky to enable by default in add-ins like BIDS Helper and BimlExpress.

So in short, if you want to restrict the length of parameters than better to create parameters manually in SSIS and not use BIML :-) Thanks Cathrine and will wait for your post to hear the long story on your blog :-)

Very well explained and detailed explanation. Just love that :-) The only one question about workaround solution mentioned above. Can we allowed to define the length of string project params? You define string parameter like below in your example. In this case length of string parameter will be default as per SSIS. Can we do something like that so that we should have a choice to define the length of project parameters? Will eagerly wait to hear back from you?

Hi Manish, you can’t restrict the length of parameters in SSIS, so you can’t do that with Biml either.

This is awesome! Thank you for providing this. It has helped me quite a bit.

Just what is was looking for! Great Work Cathrine.

Share Your Thoughts?