Package Configurations and Connection Managers in Biml

Why This Pattern Matters

A common starting point for adopting Biml on an existing project is to recreate the existing packages exactly so the change is invisible to the runtime. For projects that use the package deployment model and rely on package configurations, that means producing the same XML configuration file references, the same SQL Server configurations table entries, and the same parent package variables that the originals have.

The example walks through five pieces in order:

An XML configuration file package configuration.
The connection manager that the XML configuration file points to.
SQL Server package configurations that pull values out of the configurations table.
The connection managers that the SQL Server configurations populate.
A parent package variable.

Adding an XML Configuration File

The package configuration that points to the XML file uses 'PackageConfiguration' with an 'ExternalFileInput' child whose 'ExternalFilePath' attribute holds the file path. The XML file itself defines a property path to the connection string of a connection manager named 'ConfigStore':

<DTSConfiguration>
  <DTSConfigurationHeading>
    <DTSConfigurationFileInfo />
  </DTSConfigurationHeading>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[ConfigStore].Properties[ConnectionString]" ValueType="String">
    <ConfiguredValue>Data Source=.\SQL2019;Initial Catalog=ConfigStore;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
  </Configuration>
</DTSConfiguration>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="ConfiguredPackage" ConstraintMode="Linear">
      <PackageConfigurations>
        <PackageConfiguration Name="Configuration">
          <ExternalFileInput ExternalFilePath="C:\SSIS\Configuration.dtsConfig" />
        </PackageConfiguration>
      </PackageConfigurations>
    </Package>
  </Packages>
</Biml>

Generating the package at this point produces an error when the package opens because the 'ConfigStore' connection it expects has not been declared.

Forcing the Connection Manager to Appear

A 'Connections' element under 'Biml' declares the connection definition. A second 'Connections' element inside the 'Package' references it. Without that inner reference the compiler skips the connection manager because no task uses it, and the configuration cannot bind. The outer position of the global 'Connections' block is a matter of taste. Placing it after 'Packages' is a common convention because the SSIS designer shows connection managers below the control flow:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="ConfiguredPackage" ConstraintMode="Linear">
      <PackageConfigurations>
        <PackageConfiguration Name="Configuration">
          <ExternalFileInput ExternalFilePath="C:\SSIS\Configuration.dtsConfig" />
        </PackageConfiguration>
      </PackageConfigurations>
      <Connections>
        <Connection ConnectionName="ConfigStore" />
      </Connections>
    </Package>
  </Packages>
  <Connections>
    <OleDbConnection Name="ConfigStore" ConnectionString="Data Source=.\SQL2019;Initial Catalog=ConfigStore;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
  </Connections>
</Biml>

Adding SQL Server Configurations

Two more package configurations source their values from the SSIS Configurations table. Each one references the 'ConfigStore' connection through 'ConnectionName' and points at the same physical table through 'ExternalTableInput'. The configuration filter that splits the rows in the table for each target connection comes from the 'Name' attribute on 'PackageConfiguration', so the names must match the configuration filter values stored in the table:

<PackageConfigurations>
  <PackageConfiguration Name="Configuration">
    <ExternalFileInput ExternalFilePath="C:\SSIS\Configuration.dtsConfig" />
  </PackageConfiguration>
  <PackageConfiguration Name="StagingSource" ConnectionName="ConfigStore">
    <ExternalTableInput Table="dbo.[SSIS Configurations]" />
  </PackageConfiguration>
  <PackageConfiguration Name="WarehouseTarget" ConnectionName="ConfigStore">
    <ExternalTableInput Table="dbo.[SSIS Configurations]" />
  </PackageConfiguration>
</PackageConfigurations>

Defining the Configured Connection Managers

Two new 'OleDbConnection' definitions sit alongside the configuration store connection in the global 'Connections' block, and matching 'Connection' references inside the package force them to appear as connection managers in the generated package:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="ConfiguredPackage" ConstraintMode="Linear">
      <PackageConfigurations>
        <PackageConfiguration Name="Configuration">
          <ExternalFileInput ExternalFilePath="C:\SSIS\Configuration.dtsConfig" />
        </PackageConfiguration>
        <PackageConfiguration Name="StagingSource" ConnectionName="ConfigStore">
          <ExternalTableInput Table="dbo.[SSIS Configurations]" />
        </PackageConfiguration>
        <PackageConfiguration Name="WarehouseTarget" ConnectionName="ConfigStore">
          <ExternalTableInput Table="dbo.[SSIS Configurations]" />
        </PackageConfiguration>
      </PackageConfigurations>
      <Connections>
        <Connection ConnectionName="ConfigStore" />
        <Connection ConnectionName="StagingSource" />
        <Connection ConnectionName="WarehouseTarget" />
      </Connections>
    </Package>
  </Packages>
  <Connections>
    <OleDbConnection Name="ConfigStore" ConnectionString="Data Source=.\SQL2019;Initial Catalog=ConfigStore;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    <OleDbConnection Name="StagingSource" ConnectionString="Data Source=.\SQL2019;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    <OleDbConnection Name="WarehouseTarget" ConnectionString="Data Source=.\SQL2019;Initial Catalog=Warehouse;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
  </Connections>
</Biml>

Inheriting From a Parent Package Variable

Configuring a parent package variable in the SSIS designer requires several clicks. In Biml the same result comes from a single attribute on the variable. Add 'InheritFromPackageParentConfigurationString' with the value of the parent variable name to a 'Variable' element, and the compiler produces both the variable and the parent configuration that drives it:

<Variables>
  <Variable Name="ParentPackageID" DataType="Int32" InheritFromPackageParentConfigurationString="PackageID">0</Variable>
</Variables>

Summary

Five Biml constructs cover the most common package configuration patterns under the package deployment model: an 'ExternalFileInput' for XML configuration files, an 'ExternalTableInput' for SQL Server configurations, paired global and inner 'Connections' blocks to materialize connection managers, and 'InheritFromPackageParentConfigurationString' to consume a value from a parent package.

Why This Pattern Matters​

Adding an XML Configuration File​

Forcing the Connection Manager to Appear​

Adding SQL Server Configurations​

Defining the Configured Connection Managers​

Inheriting From a Parent Package Variable​

Summary​