Cathrine Wilhelmsen

Package Configurations and Connection Managers in Biml

Published:
Categories: Biml
Tags: ,
Reading Time: 8 min

Biml Wheel.

At work we wanted to start using Biml to speed up development in our existing projects without making too many changes at once. I decided to start by writing Biml files that generates SSIS packages exactly like the ones we already have so we can implement changes faster, and step one was to figure out how to create Package Configurations and Connection Managers in Biml:

  • Create an XML configuration file Package Configuration
  • Create the Connection Manager specified in the XML configuration file
  • Create SQL Server Package Configurations that use the Connection Manager specified in the XML configuration file
  • Create Connection Managers specified in the [SSIS Configurations] table in SQL Server
  • Create a Parent package variable

I recommend reading Understanding Integration Services Package Configurations by Rafael Salas (@RafSalas) and Getting Started with BimlScript by Paul S. Waters (@pswaters) if you’re not already familiar with the basics of SSIS Package Configurations and Biml.

For the examples in this post I have created a small Integration Services Project in Visual Studio 2013 that uses the package deployment model. It contains one SSIS package called SSISPackageConfigurations.dtsx and a Biml File called BimlPackageConfigurations.biml that will generate another SSIS package called BimlPackageConfigurations.dtsx. I also use a database called Configurations with the [SSIS Configurations] table, a database called BimlSource and a database called BimlDestination:

BimlPackageConfigurations Project.

Create an XML configuration file Package Configuration

In all our environments (development / test / production) and on our development computers we have an XML configuration file located at C:\SSIS\Configuration.dtsConfig:

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

In each environment it contains the Configuration for the Connection Manager to the server and database with the [SSIS Configurations] table where all other configurations are stored. First we need to add the Package Configuration pointing to this XML configuration file.

In SSIS I would open Package Configurations Organizer (SSIS → Package Configurations / right-click in Control Flow → Package Configurations), enable package configurations, click add, choose Configuration type XML configuration file, click Browse, browse to my XML configuration file, click Next, click Reuse Existing, call it Configuration, click Finish and click Close:

SSIS XML configuration file Package Configuration.

In Biml I would add a PackageConfiguration to the Package:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="BimlPackageConfigurations" ConstraintMode="Linear">

      <!-- Added: PackageConfigurations -->
      <PackageConfigurations>
        <PackageConfiguration Name="Configuration">
          <ExternalFileInput ExternalFilePath="C:\SSIS\Configuration.dtsConfig" />
        </PackageConfiguration>
      </PackageConfigurations>
      <!-------------->

    </Package>
  </Packages>
</Biml>

The PackageConfiguration element named Configuration has an ExternalFileInput element with the ExternalFilePath attribute pointing to the location of my XML configuration file.

If you right-click on BimlPackageConfigurations.biml in the Visual Studio Project and click Check Biml for Errors, you will not get any warnings and you can right-click and click Generate SSIS Packages. However, when you open the new BimlPackageConfigurations.dtsx package (or close and re-open the SSISPackageConfigurations.dtsx package) you will get the error “The connection “Configurations” is not found. This error is thrown by Connections collection when the specific connection element is not found.” This is because we haven’t yet created the actual Connection Manager.

Create the Connection Manager specified in the XML configuration file

Next in SSIS I would right-click in Connection Managers, click New OLE DB Connection, click New, use the server and database specified in the XML configuration file, click OK, click OK again and rename the newly created Connection Manager to Configurations. The Connection Manager will then get the aqua triangle icon on it that indicates that a Package Configuration is used:

SSIS Connection Manager.

In Biml I would add the Connections element below the Packages element:

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

  <!-- Added: Connections -->
  <Connections>
    <OleDbConnection Name="Configurations" ConnectionString="Data Source=.\SQL2012;Initial Catalog=Configurations;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
  </Connections>
  <!-------------->

</Biml>

I like to add the Connections element after the Packages element simply because in BIDS / SSDT-BI the Connection Managers are located below the Control Flow, you can add this above the Packages element if you prefer. The attribute Name in the OleDbConnection element is the name we want to use for the Connection Manager, and the ConnectionString attribute has the same value as specified in the XML configuration file.

Again, if you Check Biml for Errors, you will not get any warnings and you can Generate SSIS Packages. However, when you reopen the BimlPackageConfigurations.dtsx package you will still get the same error: “The connection “Configurations” is not found. This error is thrown by Connections collection when the specific connection element is not found.” This is because Biml only creates the tasks and components necessary in the SSIS package. Since we haven’t yet created any tasks that use the Connection Manager we must force Biml to create the Connection Manager by adding a Connection inside the Packages element as well:

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

      <!-- Added: Connections -->
      <Connections>
        <Connection ConnectionName="Configurations" />
      </Connections>
      <!-------------->

    </Package>
  </Packages>
  <Connections>
    <OleDbConnection Name="Configurations" ConnectionString="Data Source=.\SQL2012;Initial Catalog=Configurations;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
  </Connections>
</Biml>

The ConnectionName attribute in the Connection element refers to the OleDbConnection attribute Name. When you Generate SSIS Packages and reopen the BimlPackageConfigurations.dtsx package you will now see the Connection Manager named Configurations with the aqua triangle icon on it that indicates that a Package Configuration is used:

SSIS Connection Manager.

Create SQL Server Package Configurations that use the Connection Manager specified in the XML configuration file

Next I want to create the Connection Managers that will be used in the data flow source and destination, and these Connection Managers should get their values from the [SSIS Configurations] table.

In SSIS I would open Package Configurations Organizer (SSIS → Package Configurations / right-click in Control Flow → Package Configurations), click add, choose the SQL Server configuration type, choose the Configurations connection, choose the [dbo].[SSIS Configurations] table, choose the BimlSource filter, click Next, click Reuse Existing, call it BimlSource, click Finish, repeat all the steps for BimlDestination and click Close:

SSIS SQL Server Package Configurations.

In Biml I would add two new PackageConfigurations:

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

        <!-- Added: PackageConfigurations -->
        <PackageConfiguration Name="BimlSource" ConnectionName="Configurations">
          <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
        </PackageConfiguration>
        <PackageConfiguration Name="BimlDestination" ConnectionName="Configurations">
          <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
        </PackageConfiguration>
        <!-------------->

      </PackageConfigurations>
      <Connections>
        <Connection ConnectionName="Configurations" />
      </Connections>
    </Package>
  </Packages>
  <Connections>
    <OleDbConnection Name="Configurations" ConnectionString="Data Source=.\SQL2012;Initial Catalog=Configurations;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
  </Connections>
</Biml>

These PackageConfigurations have ConnectionName attributes that refer to the OleDbConnection named Configurations. They have ExternalTableInput elements with a Table attribute that refers to the [SSIS Configurations] table. The most important thing to notice is that the Name attribute is also used as the filter in the [SSIS Configurations] table:

SSIS and Biml SQL Server Package Configurations.

Create Connection Managers specified in the [SSIS Configurations] table in SQL Server

Next we have to repeat the steps of actually creating the Connection Managers.

In SSIS I would right-click in Connection Managers, click New OLE DB Connection, click New, use server .\SQL2012 and database BimlSource, click OK, click OK again and rename the newly created Connection Manager to BimlSource. I would do the same for the BimlDestination Connection Manager.

In Biml I would add two new OleDbConnections and force the creation of the Connection Managers by adding two new Connections to the Package:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="BimlPackageConfigurations" ConstraintMode="Linear">
      <PackageConfigurations>
        <PackageConfiguration Name="Configuration">
          <ExternalFileInput ExternalFilePath="C:\SSIS\Configuration.dtsConfig" />
        </PackageConfiguration>
        <PackageConfiguration Name="BimlSource" ConnectionName="Configurations">
          <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
        </PackageConfiguration>
        <PackageConfiguration Name="BimlDestination" ConnectionName="Configurations">
          <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
        </PackageConfiguration>
      </PackageConfigurations>
      <Connections>
        <Connection ConnectionName="Configurations" />

        <!-- Added: Connections -->
        <Connection ConnectionName="BimlSource" />
        <Connection ConnectionName="BimlDestination" />
        <!-------------->

      </Connections>
    </Package>
  </Packages>
  <Connections>
    <OleDbConnection Name="Configurations" ConnectionString="Data Source=.\SQL2012;Initial Catalog=Configurations;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />

    <!-- Added: OleDbConnections -->
    <OleDbConnection Name="BimlSource" ConnectionString="Data Source=.\SQL2012;Initial Catalog=BimlSource;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    <OleDbConnection Name="BimlDestination" ConnectionString="Data Source=.\SQL2012;Initial Catalog=BimlDestination;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    <!-------------->

  </Connections>
</Biml>

When you Generate SSIS Packages and reopen the BimlPackageConfigurations.dtsx package you will see the two new Connection Managers named BimlSource and BimlDestination with aqua triangle icons on them that indicates that Package Configurations are used:

SSIS Connection Managers.

Create a Parent package variable

Finally I would like to add a Parent package variable that gets the PackageID from the parent package and stores it in the variable ParentPackageID.

In SSIS I would first create a variable named ParentPackageID:

Parent Package Variable.

Then I would open Package Configurations Organizer (SSIS → Package Configurations / right-click in Control Flow → Package Configurations), click add, choose the Parent package variable configuration type, specify the Parent variable PackageID, click Next, browse to the ParentPackageID Variable and choose the Value property, click Next, call it ParentPackageID, click Finish and click Close:

Parent Package Configurations.

Parent Package Configuration.

The variable named ParentPackageID will now have the aqua triangle icon on it that indicates that a Package Configuration is used:

Parent Package Variable configured in Package Configurations.

In Biml this is a lot easier! You only need to add a Variable named ParentPackageID, set the required attribute DataType to Int32, and then add the attribute InheritFromPackageParentConfigurationString with the value PackageID:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="BimlPackageConfigurations" ConstraintMode="Linear">
      <PackageConfigurations>
        <PackageConfiguration Name="Configuration">
          <ExternalFileInput ExternalFilePath="C:\SSIS\Configuration.dtsConfig" />
        </PackageConfiguration>
        <PackageConfiguration Name="BimlSource" ConnectionName="Configurations">
          <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
        </PackageConfiguration>
        <PackageConfiguration Name="BimlDestination" ConnectionName="Configurations">
          <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
        </PackageConfiguration>
      </PackageConfigurations>
      <Connections>
        <Connection ConnectionName="Configurations" />
        <Connection ConnectionName="BimlSource" />
        <Connection ConnectionName="BimlDestination" />
      </Connections>

      <!-- Added: Variables -->
      <Variables>
        <Variable Name="ParentPackageID" DataType="Int32" InheritFromPackageParentConfigurationString="PackageID">0</Variable>
      </Variables>
      <!-------------->

    </Package>
  </Packages>
  <Connections>
    <OleDbConnection Name="Configurations" ConnectionString="Data Source=.\SQL2012;Initial Catalog=Configurations;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    <OleDbConnection Name="BimlSource" ConnectionString="Data Source=.\SQL2012;Initial Catalog=BimlSource;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    <OleDbConnection Name="BimlDestination" ConnectionString="Data Source=.\SQL2012;Initial Catalog=BimlDestination;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
  </Connections>
</Biml>

When you Generate SSIS Packages and re-open the BimlPackageConfigurations.dtsx package, you will see that you have got a Parent package configuration and the ParentPackageID variable with the aqua triangle, just by adding the attribute InheritFromPackageParentConfigurationString to a Variable.

Summary

This post is a basic introduction to Package Configurations and Connection Managers in Biml. It shows how to add an XML configuration file Package Configuration that points to the [SSIS Configurations] table where all other configurations are stored, how to get the other configurations from the [SSIS Configurations] table, and how to create the Connection Managers used in the package. It also shows how to add a Parent package variable. I hope new Biml developers will find it useful!

Share or Comment?

About the Author

Professional headshot of Cathrine Wilhelmsen.Cathrine Wilhelmsen is a Microsoft Data Platform MVP, international speaker, author, blogger, organizer, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, gaming, coffee and chocolate 🤓