Resolving the Biml project level connection manager issue

Biml connection manager ids broken

Well, it's not quite that dire but it sure can seem like it. You build out N packages that use project level connection managers and all is well and good until you open them up and they're all angry with red Xs in them. Or as this person encountered, the OLE DB Source defaulted to the first connection manager and table it found.

Root cause

I say root cause without looking at source code because the guys at Varigence are way smarter than I can hope to be. I can however look at cause and effect and mitigate as I can. What I see as happening is that the packages that get generated in round 1 have their connection manager ids (ugly guids inside the XML) set and those match the Project level Connection Manager and all is good. You then generate more packages and depending on whether you overwrite the existing Connection Managers, will determine whether you break the existing packages or the new ones. Sophie's choice, eh?

The good thing, is that there's a fairly simple approach to solving this issue. For your project level connection managers, assign an explicit GUID and then reference that same guid in your packages. This approach will require tiering but it at least "works on my computer".

Environments.biml

Since we'll provide no explicit tier and there no script in there, this will be tier 0. If you need to dynamically define the values in your environment file, just be sure it is a lower tier than the subsequent files. In this file, we simply enumerate our Connections. You'll see I have two connections: one project level, one not.

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

Projects.biml

There is no attribute in the Connections collection to assign a guid. It's simply not there. If you want to associate an Id with an instance of a Connection your choices are the Project node and the Package node. Since we're dealing with project level connection managers, we best cover both bases to ensure Ids synchronize across our project. If you wish, you could have embedded this Projects node in with the Connections but then you'd have to statically set these Ids. I feel like showing off so we'll go dynamic.

To start, I define a list of static GUID values in the beginning of my file. Realistically, we have these values in a table and we didn't go with "known" values. The important thing is that we will always map a guid to a named connection manager. If you change a connection manager's definition from being project level to non, or vice versa, this will result in the IDs shifting and you'll see the same symptoms as above.

I use Biml to inspect itself, thus the need for tiering, and for all the Connection managers I find that satisfy the criteria of CreateInProject == true, I want to define them within my Connections collection inside my PackageProject node.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <#
    // Build a repeatable list of GUIDs
    List<Guid> staticGuids = new List<Guid>();
    staticGuids.Add(Guid.Parse("DEADBEEF-DEAD-BEEF-DEAD-BEEFDEADBEEF"));
    staticGuids.Add(Guid.Parse("DEADBEEF-DEAD-BEEF-DEAD-BEEF8BADF00D"));
    staticGuids.Add(Guid.Parse("DEADBEEF-DEAD-BEEF-DEAD-D15EA5EBAD00"));
    staticGuids.Add(Guid.Parse("FEEDFACE-DEAD-BEEF-FACE-FEED00000000"));
    #>
    <Projects>
        <PackageProject Name="ConnectionManagerIssue">
            <Connections>
                <# 
                    Guid g = new Guid();
                    // Only generate a Connection node for project level connections
                    foreach (var item in RootNode.Connections.Where(x => x.CreateInProject))  
                    {
                        // Pop the first element so we don't repeat
                        g = staticGuids[0];
                        staticGuids.RemoveAt(0);
                #>
                    <Connection ConnectionName="<#=item.Name#>" Id="<#= g.ToString().ToUpper() #>" />
                <#
                }
                #>
            </Connections>
        </PackageProject>
    </Projects>
</Biml>

Packages.biml

We'll use much the same trick except that now we'll inspect the Projects node to find all the connection managers. By default, we'll only have the Project level ones defined there so it's a bit easier. Build the package as normal but at the bottom, stub in the Connections collection and then populate the connection managers with their Ids. Since I'm lazy, I'm going to just call GetBiml for all the connection managers I find in the Projects node collection since they have the same attributes of Name and Id.

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

Wrap up

I tack the Connection logic at the bottom of my packages because when Varigence gets the next release of BimlExpress out, I expect this will be resolved so I can just snip that unwanted code out.

Happy Biml'ing