Create SSIS Project Parameters from Biml

The Gap in BIDS Helper and BimlExpress

Free Biml builds in BIDS Helper and BimlExpress generate SSIS package files but stop short of generating project parameters. Biml accepts 'Parameter' nodes inside a 'PackageProject', and the build tools read them, but the resulting parameters never reach the Project.params file in the SSIS project. The official guidance is to define project parameters by hand before generating the packages.

That works for a small project, but it falls apart when project parameters need to be driven by metadata or kept in sync with a generated solution. The workaround in this walkthrough writes the parameter XML directly to Project.params from BimlScript, so a project parameter list can stay generated alongside the packages.

Why Project.params Is Approachable

Project.params is a plain XML file in the SSIS project folder. Each project parameter is a 'SSIS:Parameter' element with a small set of properties that describe the data type, value, sensitivity, required flag, and creation name. Because the file is plain text, BimlScript can build the XML in a string and overwrite the file at compile time.

Two Biml files split the work:

• The first file declares the parameters in a 'PackageProject' and records the on disk path of Project.params in an annotation.
• The second file is a tiered utility that reads that annotation, builds the XML, and writes the file.

Declaring the Parameters

The first file lists the project parameters and stores the absolute Project.params path in an annotation tag named 'ProjectParametersPath'. The annotation is the contract between the declaration file and the writer:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Projects>
    <PackageProject Name="OrderIngestProject">
      <Annotations>
        <Annotation Tag="ProjectParametersPath">C:\SSIS\OrderIngestProject\Project.params</Annotation>
      </Annotations>
      <Parameters>
        <Parameter Name="EnableAudit" DataType="Boolean" IsRequired="false" IsSensitive="false">0</Parameter>
        <Parameter Name="WindowStartDate" DataType="DateTime" IsRequired="false" IsSensitive="false">2024-01-01</Parameter>
        <Parameter Name="ThresholdAmount" DataType="Decimal" IsRequired="false" IsSensitive="false">9.95</Parameter>
        <Parameter Name="BatchSize" DataType="Int32" IsRequired="true" IsSensitive="false">5000</Parameter>
        <Parameter Name="ApiToken" DataType="String" IsRequired="false" IsSensitive="true">change-me</Parameter>
      </Parameters>
    </PackageProject>
  </Projects>
</Biml>

Writing Project.params at Build Time

The second file is a tier 1000 BimlScript that runs after the declaration file has been parsed. It locates the project, reads the path annotation, builds the SSIS:Parameters XML in a StringBuilder, and overwrites the Project.params file:

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

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

      try {

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

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

        File.WriteAllText(paramsPath, paramXml.ToString());

      } catch (Exception e) {
        ValidationReporter.Report(pkgProject, 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", paramsPath));
      }

    }
  }
#>

The tier directive makes sure the writer runs after the declaration file has been processed, so the 'PackageProject' and its parameter list are present in the model when the writer executes.

Generating the Project Parameters

Add both Biml files to an SSIS project that uses the Project Deployment Model. Select both files, right click, and choose Generate SSIS Packages. Visual Studio detects that Project.params has changed on disk and prompts to reload it. Accept the reload, and the new project parameters appear in the SSIS project as if they had been entered by hand.

When This Pattern Pays Off

The technique is most useful when the parameter list is driven by metadata, when the same parameter set has to be reproduced across many SSIS projects, or when generated parameters need to stay in lockstep with generated packages. For a small static parameter list, defining the parameters once in the SSIS designer is still the simplest path.