Create SSIS 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:

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.

Project Parameters from Biml

The Biml solution is split in two Biml files to make it easy to use and maintain.

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 😊

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, international speaker, author, blogger, organizer, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, gaming, coffee and chocolate 🤓