Package Configurations and Connection Managers in Biml

Biml (Business Intelligence Markup Language) - Package Configurations and Connection Managers in BimlAt 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 (b | t) and Getting Started with BimlScript by Paul S. Waters (t) 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:

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:

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:

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:

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:

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:

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:

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.

Conclusion
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!

Who is Cathrine Wilhelmsen?

Cathrine is a Microsoft Data Platform MVP, BimlHero, Microsoft Certified Solutions Expert, author, speaker, blogger and chronic volunteer who loves teaching and sharing knowledge. She works as a consultant, architect and developer, focusing on Data Warehouse and Business Intelligence projects. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

22 thoughts on “Package Configurations and Connection Managers in Biml”

Very useful, interesting and constructive post. Many thanks for your detailed information.

This was a very useful post. Do you know if this approach is possible in the project deployment model? Seems to me that the project deployment model uses package parameters and environments instead of configurations.

Hi Bob, I’m happy to hear that you found it useful. I wrote this post based on an SSIS 2008 project that uses the package deployment model. We are currently upgrading from SQL Server 2008 to SQL Server 2014 and I have actually not yet tried using Biml with the project deployment model myself, but I hope to write a blog post about it early next year.

Hi,

I am new to the SSIS. I am trying to load multiple tables from my staging database to destination tables in the data warehouse. My staging database is nothing but a database with all the tables from all my source databases copied to it. I am having 100 of SSIS package of 100 Tables. I want to make single SSIS package to load all the 100 tables using single DFT. I have a filmaster table which containing the list of source table and destination table name, and input will be the filename , on the basis of filename my DFT will take the source and derstination table and run the package accordingly.

Can I achive above scenario using BIML Script, if yes please provide some help.

Regards,
Vipin Jha

Hi Vipin. This is easy to achieve with Biml and BimlScript. I would suggest that you start with the Getting Started with Biml series on BimlScript.com and the Stairway to Biml series on SQL Server Central. If you prefer live demos you can watch Scott Currie’s great Create and Load a Staging Environment from Scratch in an Hour presentation. I hope this will help you get started using Biml and BimlScript, good luck!

Hi Catherine,

I am having a little trouble writing the Biml to use an xml configuration file (to update a connection manager) without going through the SQL Server config route. Is this possible?

I have basically written the first part of the code that scripts the connection, the xml package configuration and performs an Execute SQL Task; but when I generate the package my connection is not using the config file.

Regards
Dave

Hello Catherine,

I just want to express my appreciation for a very useful, clear and well written article.

Ron.

Catherine, Excellent post. Have you had any success using Bids Helper 1.7 with package configurations? It appears that something has changed and it no longer creates the package configurations,

Hi Kris, I know others have also had problems with package configurations, but I’m afraid I haven’t been able to reproduce the errors myself. I use SQL Server 2014, Visual Studio 2013 and BIDS Helper 1.7, and my package configurations are generated without errors. (Both on my personal laptop and work computer.) I can’t promise I’ll be able to help you, but I’d be happy to take a look at your code if you want. You can find my e-mail address on my contact page :)

thax’s Cathrine, this is what I’m looking for
It makes me live much easier

Thanks, Cathrine .. this post was most insightful, given that most other BIML articles deal with simple connection managers which don’t involve migrating through environments.

I do have one question: the RootNode Connections object connection strings still seem to be hardcoded in the example. Would we need to use a little extra BIMLScript to populate these connection strings based on where the XML configuration file is located?

This is a FANTASTIC BIML resource site!!

Another conundrum … My “classic” ETL uses an XML config file to populate package variables with the connection strings, which are then used in expressions scattered about the packages. I’m thinking that this approach can be eliminated by building with Biml. Just not sure how to get the connection information read from the config file (or table preferably) into the Biml stream. Any guidance? Thanks!

Hi Bill, I usually compare Biml development to SSIS development. (Biml is just a different way of developing SSIS packages, instead of using the GUI.) When developing an SSIS package, you will need to specify hardcoded values for all connection strings so SSIS can test the connection and read metadata and so on. However, when you use package configurations, the hardcoded values in the SSIS package is replaced by external values when you open up the SSIS package. Biml works the same way. You will have to provide the hardcoded values in the Biml file to be able to generate the SSIS package without errors, but when you open up the generated SSIS package, all the hardcoded values will be replaced. Does that make sense?

Great article Cathrine… if it could add sample BIML script method for third party components like SSIS Connectors v3.0 for Oracle Microsoft Oracle Source Component by Attunity with SSIS 2014… It could be great help.

Thanks

Hi Bhudev, that’s a completely different blog post… :) I will not be adding third-party components to this blog post, but I will add the suggestion to my list of future blog post ideas. Thanks!

Great article… but only one think is missing is “Configuration Filter” how to configure that ?

From the blog post: “The most important thing to notice is that the Name attribute is also used as the filter in the [SSIS Configurations] table” :)

Great starting blog in creating a SSIS package with some useful things. I am wondering if as Bhudev mentions if there is a way to have a different value for the ConfigurationFilter from the Name attribute?

Hi Cathrine,

I have a connection in the SSIS connection Managers and I am trying to access this connection and call it in my biml script, but I could not achieve that. Here a sample code that I am working with:

<OleDbSource Name="Get Persons" ConnectionName="”>

<OleDbDestination Name="Load Persons" ConnectionName="”>

Thanks in advance!

Sorry part of the code was ignored :\

<OleDbSource Name="Get Persons" ConnectionName="”>

<OleDbDestination Name="Load Persons" ConnectionName="”>

The same problem happended again :|. I can’t post the code.

I hope the blog won’t drop this line :)

I used this script to access the connection in the Connnection Managers, but unfortunately it does not work.

Since the bolg prevent adding a code example in the script, I asked the question in stackoverflow. https://stackoverflow.com/questions/47508801/use-an-already-created-connection-in-connection-managers-into-biml-script
I hope Ithat would be convenient for you :)

Thanks,
Ziad

Share Your Thoughts?

This site uses Akismet to reduce spam. Learn how your comment data is processed.