Skip to content

Create SSIS Project Parameters from Biml

Biml (Business Intelligence Markup Language) - Project Parameters from Biml

If 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:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Projects>
    <PackageProject Name="ProjectParameters">
      <Annotations>
        <Annotation Tag="ProjectParametersPath">C:\SSIS\TestProject\Project.params</Annotation>
      </Annotations>
      <Parameters>
        <Parameter Name="ParameterBoolean" DataType="Boolean" IsRequired="false" IsSensitive="false">0</Parameter>
        <Parameter Name="ParameterDateTime" DataType="DateTime" IsRequired="false" IsSensitive="false">2015-11-04</Parameter>
        <Parameter Name="ParameterDecimal" DataType="Decimal" IsRequired="false" IsSensitive="false">3.14</Parameter>
        <Parameter Name="ParameterInt32" DataType="Int32" IsRequired="true" IsSensitive="false">1337</Parameter>
        <Parameter Name="ParameterString" DataType="String" IsRequired="false" IsSensitive="true">password</Parameter>
      </Parameters>
    </PackageProject>
  </Projects>
</Biml>

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:

<#@ template tier="1000" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="Varigence.Flow.FlowFramework.Validation" #>

<# 
  var project = RootNode.PackageProjects.FirstOrDefault();
  if (project == null) {
    ValidationReporter.Report(Severity.Error, "<PackageProject> does not exist");
  } else {
    var projectPath = project.GetTag("ProjectParametersPath");
    if (projectPath == "") {
      ValidationReporter.Report(project, Severity.Error, "Annotation ProjectParametersPath does not exist", @"Add <Annotation Tag=""ProjectParametersPath"">C:\SSIS\TestProject\Project.params</Annotation> to <PackageProject>");
    } else {

      try {

      var fileAttributes = File.GetAttributes(projectPath);
      if ((fileAttributes & FileAttributes.ReadOnly) == FileAttributes.ReadOnly) {
        File.SetAttributes(projectPath, (fileAttributes & ~FileAttributes.ReadOnly));
      }

      StringBuilder parameters = new StringBuilder();
      parameters.AppendLine("<?xml version=\"1.0\"?>");
      parameters.AppendLine("<SSIS:Parameters xmlns:SSIS=\"www.microsoft.com/SqlServer/SSIS\">");
      foreach (var parameter in project.Parameters) {
        parameters.AppendFormat("<SSIS:Parameter SSIS:Name=\"{0}\">", parameter.Name).AppendLine();
        parameters.AppendLine("<SSIS:Properties>");
        parameters.AppendFormat("<SSIS:Property SSIS:Name=\"ID\">{{{0}}}</SSIS:Property>", (parameter.Id == Guid.Empty ?  Guid.NewGuid() : parameter.Id)).AppendLine();
        parameters.AppendFormat("<SSIS:Property SSIS:Name=\"DataType\">{0}</SSIS:Property>", Convert.ToByte(parameter.DataType)).AppendLine();
        parameters.AppendFormat("<SSIS:Property SSIS:Name=\"Value\">{0}</SSIS:Property>", parameter.Value).AppendLine();
        parameters.AppendFormat("<SSIS:Property SSIS:Name=\"Sensitive\">{0}</SSIS:Property>", Convert.ToByte(parameter.IsSensitive)).AppendLine();
        parameters.AppendFormat("<SSIS:Property SSIS:Name=\"Required\">{0}</SSIS:Property>", Convert.ToByte(parameter.IsRequired)).AppendLine();
        parameters.AppendFormat("<SSIS:Property SSIS:Name=\"IncludeInDebugDump\">{0}</SSIS:Property>", Convert.ToByte(parameter.IncludeInDebugDump)).AppendLine();
        parameters.AppendFormat("<SSIS:Property SSIS:Name=\"Description\">{0}</SSIS:Property>", parameter.GetTag("Description")).AppendLine();
        parameters.AppendFormat("<SSIS:Property SSIS:Name=\"CreationName\">{0}</SSIS:Property>", parameter.GetTag("CreationName")).AppendLine();
        parameters.AppendLine("</SSIS:Properties>");
        parameters.AppendLine("</SSIS:Parameter>");
      }
      parameters.AppendLine("</SSIS:Parameters>");

      File.WriteAllText(projectPath, parameters.ToString());

      } catch (Exception e) {
        ValidationReporter.Report(project, Severity.Error, "Error writing Project Parameters to Project.params", String.Format("Make sure the path \"{0}\" is correct and that this project uses the Project Deployment Model", projectPath));
      }

    }
  }
#>

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