Use an already created connection in "Connection Managers" into Biml script

Question

I have an already source connection in SSIS "Connection Managers". It is a custom connection (not a oledb or any other standard connection types). Is it possible to reuse it (i.e call it) in the Biml script without writing the connetion string again?

I saw two ways on the internet but neither worked with me: I did an example to connect to Advanturwork database, bellow are the screenshots of the project and the error message as well as the code of the two ways.

Here is the porject files.

This is the connection Managers XML file (MyConn.conmgr).

Now comes the Biml code (way 1):

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Package1" ConstraintMode="Linear" >
            <Connections>
                <Connection ConnectionName="MyConn" Id="FB58654F-7992-4DB2-9057-38595A89B6BF"></Connection>
            </Connections>
            <Tasks>
                <Dataflow Name="DF1">
                    <Transformations>
                        <OleDbSource Name="Get Data" ConnectionName="MyConn">
                            <ExternalTableInput Table="Person.Person"/>
                        </OleDbSource>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

The error I get is here:

The second Biml code (way 2):

<#@ import namespace="System" #>
<#@ import namespace="System.Data" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <# var Source = (AstDbConnectionNode)RootNode.Connections["MyConn"]; #>
    <Packages>
        <Package Name="Package1" ConstraintMode="Linear" >
            <Tasks>
                <Dataflow Name="DF1">
                    <Transformations>
                        <OleDbSource Name="Get Data" ConnectionName="Source">
                            <ExternalTableInput Table="Person.Person"/>
                        </OleDbSource>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

The error message is the same:

P.S. MyConn in the example is an oledb but in the real project it is a custom connection which is already defind in the "Connection Managers" and I need just to use (call it) in the biml script.

Thanks, Ziad

Answer 1

Ok, now I see enough to understand the question.

Within your SSIS project, you have a project level connection manager "MyConn". This was generated outside of Biml.

Within your Biml files, you would like to reference the existing physical connection manager. That unfortunately, isn't directly possible. Biml defines all the artifacts within a project and dependencies therein. The Biml engine uses all of that to create output - which in this case would be

• Packages
• [Project level Connection Managers]
• [SSIS Project file]

[optional]

You would need to take the output (connection manager) and reverse engineer that into Biml. There are two easy approaches to this: BimlOnline.com or BimlStudio. BimlOnline is still in beta and if it works for you, fantastic. Otherwise, you need to download/register for BimlStudio and use the 15 day trial and reverse engineer the existing SSIS project (Integration Services Project 1.dtproj). That should get the Biml extracted for the custom connection manager.

If it doesn't, then it gets ugly as you have to guess/mangle your way through the syntax for your connection manager or try emailing [email protected] and see if they can't help you.

Now, once you have a working connection string, then you'll need to fix the above Biml where you use it.

First biml

In your first example, you have <OleDbSource Name="Get Data" ConnectionName="MyConn"> That will only work if you have also defined your Connection within a Connections collection. In the following Biml, I define MyConn within my Connections collection. Then, under the Packages collection, in package Package1, I provide an explicit reference to that connection manager and specify the GUID it should be assigned. This may or may not be needed in your package but my general experience is that it will not be needed.

<Biml>
    <Connections>
        <Connection Name="MyConn" ConnectionString="ABC" />
    </Connections>
    <Packages>
        <Package Name="Package1" ConstraintMode="Linear" >
        <Connections>
            <Connection ConnectionName="MyConn" Id="FB58654F-7992-4DB2-9057-38595A89B6BF"></Connection>
        </Connections>
        </Package>
    </Packages>
</Biml>

As called out way below under ramblings, you can physically separate the Connections collection into a separate file and include/multi-select it.

Second biml

Here, you're creating a C# variable, Source that is an the object version of our connection. This assumes that in an earlier tier, we have explicitly created a Connection called MyConn. Otherwise, you'll get a null reference exception because you are trying to access a member of a list (Connections) that doesn't exist.

The second issue will be when you go use it <OleDbSource Name="Get Data" ConnectionName="Source"> The Biml compiler will say that it cannot find a reference to the connection "Source". Instead, what you're looking for here is ConnectionName="<#= Source.Name #>" That directive uses the Name property of our variable Source and calls the Write method <#= #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <# var Source = (AstDbConnectionNode)RootNode.Connections["MyConn"]; #>
    <Packages>
        <Package Name="Package1" ConstraintMode="Linear" >
            <Tasks>
                <Dataflow Name="DF1">
                    <Transformations>
                        <OleDbSource Name="Get Data" ConnectionName="Source">
                            <ExternalTableInput Table="Person.Person"/>
                        </OleDbSource>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Earlier but possibly helpful Biml ramblings

I think what you're asking about is code re-use.

Within your SSIS project, you have two Biml files: Connections.biml and Packages.biml

Connections.biml looks something like (approximate)

<Biml>
    <Connections>
        <Connection Name="MyConn" ConnectionString="ABC" />
    </Connections>
</Biml>

and we have the actual Packages biml defined within the question above. The Packages.biml file can either have a Connections collection defined within it or we can reference and existing file. There are multiple ways to do this with BimlExpress.

Multi-click option

In Visual Studio, multi-select (Control, Left-Click) the Connections.biml and Package.biml files and choose Generate SSIS Packages.

Reeves has a great explanation of tiering and how all that works but the short is that all the static files will compile first and be available for reference. Then any Biml files with code in them like Packages.biml has.

You can multi-select as many as you want - my typical project has a Connections.biml file and usually a Packages.biml (which builds out worker packages) and a Master.biml (which handles the orchestration of package execution).

Importing files

There are different mechanisms for importing files into Biml but the two most common will be the include directive and the CallBimlScript function. Include can be thought of as a macro expansion - the <#@ include file="Connection.biml"#> is going to be replaced with the contents of that file.

<#=CallBimlScript("Connection.biml")#> can be thought of as a function call - because it is. I could have properties (arguments) defined in the referenced file that I pass in to control the biml that is generated.

In either case, the entities won't yet exist/be reference-able within the biml project e.g. RootNode.Connections["MyConnection"] because they are being compiled in this tier.

BimlStudio

And if you decide to purchase BimlStudio, a third approach is to set your Connections.biml file as a live biml script (or execute the Connections.biml file) to have persistent, in-memory objects for reference.