Stable Project-Level Connection Manager IDs in Biml

What Goes Wrong

Generating a batch of packages that use project-level connection managers can leave the older packages, the newer packages, or both with broken connection bindings. Symptoms include OLE DB sources that have defaulted to the first available connection manager and table, red Xs in the SSIS designer, and packages that silently use the wrong connection at runtime.

The reason is that the package XML references the project-level connection by an internal GUID. When the project-level connection is regenerated, the new GUID may or may not match the GUID embedded in any given package. Whichever set is regenerated last keeps its bindings, the other set loses them.

The reliable fix is to assign explicit, stable GUIDs to the project-level connections and to write the same GUIDs into every package that uses them.

File Layout

The fix uses three Biml files in three tiers so the connection definitions, the project node, and the packages can each see what the prior tier produced. Tier 0 declares the connections. Tier 10 declares the project and the project-level connection bindings. Tier 20 declares the packages and references the same GUIDs.

Tier 0: Connections

The connections file is plain Biml with no script. One connection is project level, one is package level:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <AdoNetConnection
      Name="ADO_Catalog"
      ConnectionString="Data Source=localhost\warehouse;Integrated Security=SSPI;Connect Timeout=30;Database=msdb;"
      Provider="SQL" />
    <OleDbConnection
      Name="OLE_Workspace"
      ConnectionString="Data Source=localhost\warehouse;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;"
      CreateInProject="true" />
  </Connections>
</Biml>

CreateInProject="true" marks OLE_Workspace as a project-level connection. ADO_Catalog stays at the package level.

Tier 10: Project With Pinned IDs

The project file inspects what RootNode already knows about the connections. For every connection that was declared with CreateInProject="true", it emits a Connection node inside the PackageProject and assigns a GUID from a predeclared list. In production, the GUIDs would live in a database; for clarity, the example hard-codes them:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <#
    List<Guid> reservedGuids = new List<Guid>();
    reservedGuids.Add(Guid.Parse("11111111-1111-4111-A111-111111111111"));
    reservedGuids.Add(Guid.Parse("22222222-2222-4222-A222-222222222222"));
    reservedGuids.Add(Guid.Parse("33333333-3333-4333-A333-333333333333"));
    reservedGuids.Add(Guid.Parse("44444444-4444-4444-A444-444444444444"));
  #>
  <Projects>
    <PackageProject Name="WarehouseLoads">
      <Connections>
        <#
          Guid pinned = new Guid();
          foreach (var conn in RootNode.Connections.Where(x => x.CreateInProject))
          {
            pinned = reservedGuids[0];
            reservedGuids.RemoveAt(0);
        #>
        <Connection ConnectionName="<#=conn.Name#>" Id="<#=pinned.ToString().ToUpper()#>" />
        <#
          }
        #>
      </Connections>
    </PackageProject>
  </Projects>
</Biml>

Switching a connection between project level and package level shifts the order in which RootNode walks the connections and changes which GUID is assigned. Avoid that by either fixing the GUID per connection name or by sorting the connection list deterministically before assignment.

Tier 20: Packages Reusing the Same IDs

The packages file runs at the highest tier so the project node is already populated. Each generated package emits a Connections block that lists every project-level connection the project knows about. GetBiml on the existing connection reference returns the right XML, including the pinned Id:

<#@ template tier="20" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# foreach (int idx in Enumerable.Range(0, 5)) { #>
    <Package Name="StableConn_<#=idx.ToString("00")#>">
      <Variables>
        <Variable DataType="String" Name="QuerySource">SELECT 100 AS Marker;</Variable>
        <Variable DataType="Int32"  Name="RowCountSource">0</Variable>
      </Variables>
      <Tasks>
        <Dataflow Name="DFT Smoke Test">
          <Transformations>
            <OleDbSource ConnectionName="OLE_Workspace" Name="Source">
              <VariableInput VariableName="User.QuerySource" />
            </OleDbSource>
            <RowCount VariableName="User.RowCountSource" Name="RC Source" />
          </Transformations>
        </Dataflow>
      </Tasks>
      <Connections>
        <#
          foreach (var connRef in RootNode.Projects
              .SelectMany(p => p.AllDefinedSuccessors())
              .OfType<AstConnectionReferenceBaseNode>())
          {
            WriteLine(connRef.GetBiml());
          }
        #>
      </Connections>
    </Package>
    <# } #>
  </Packages>
</Biml>

Each generated package now references the project-level connection by the same GUID the project emitted. Regenerating the packages or the project, in any order, leaves the bindings intact.

When the Workaround Can Be Removed

Once the BimlExpress release in use no longer rewrites the connection-manager GUIDs between regenerations, the inner Connections block inside each Package can be removed. Until then, pinning the GUIDs is the lowest-friction way to keep the generated packages bound to the right project connections.