Package Configurations with BIML

Case
I want to use SSIS Package configurations in my BIML script. How do I do that?

Solution
Here are a couple of examples of the most used package configurations. Screens are from SSIS 2012 package deployment, but it works the same in SSIS 2008.

Environment Variable Config
I have one Connection Manager named Meta and I added package configuration to get its connectionstring from a Windows Environment Variable. That variable already exists and contains a connectionstring. The screens are what the BIML script below will produce.

Environment Variable Config

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 
 <Connections>
  <!-- My Connection Manager to the Meta database containing a config table and other tables-->
  <OleDbConnection
   Name="Meta"
   ConnectionString="Data Source=.;Initial Catalog=Meta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
  </OleDbConnection>
 </Connections>
 
 <Packages>
  <Package Name="Child01" ConstraintMode="Linear">

   <PackageConfigurations>
    <!-- Environment Variable Configuration -->
    <!-- The Environment Variable should already contain a value -->

    <!-- The name of the configuration shown in the Package Configurations Organizer window -->
    <PackageConfiguration Name="SSISMeta">
     <!-- The name of the environment variable -->
     <EnvironmentVariableInput EnvironmentVariable="SSISMeta" />
     <ConfigurationValues>
      <!-- PropertyPath contains the name of the connection manager -->
      <!-- You can leave the value property empty -->
      <ConfigurationValue
       DataType="String"
       Name="ConnectrionStringMeta"
       PropertyPath="\Package.Connections[Meta].Properties[ConnectionString]"
       Value="" />
     </ConfigurationValues>
    </PackageConfiguration>

   </PackageConfigurations>

   <Tasks>
    <!-- Dummy Task with connection to make sure the connection manager is added to the package -->
    <ExecuteSQL
     Name="SQL - Dummy"
     ConnectionName="Meta"
     ResultSet="None">
     <DirectInput>
      SELECT @@VERSION AS 'SQL Server Version'
     </DirectInput>
    </ExecuteSQL>
    
   </Tasks>
  </Package>
 </Packages>
</Biml>

Download

SQL Server Configuration
I have a second Connection Manager named Source and I added package configuration to get its value from a SQL Server configuration table. This configuration table is stored in the Meta database. Note: the configurations should already exist in that table

SQL Server Config

<Biml xmlns="http://schemas.varigence.com/biml.xsd">

 <Connections>
  <!-- My Connection Manager to the Meta database containing a config table and other tables-->
  <OleDbConnection
   Name="Meta"
   ConnectionString="Data Source=.;Initial Catalog=Meta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
  </OleDbConnection>
  <!-- My Connection Manager to a source database -->
  <OleDbConnection
   Name="Source"
   ConnectionString="Data Source=.;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
  </OleDbConnection>
 </Connections>

 <Packages>
  <Package Name="Child01" ConstraintMode="Linear">

   <PackageConfigurations>
    <!-- Environment Variable Configuration -->
    <!-- The Environment Variable should already contain a value -->

    <!-- The name of the configuration shown in the Package Configurations Organizer window -->
    <PackageConfiguration Name="SSISMeta">
     <!-- The name of the environment variable -->
     <EnvironmentVariableInput EnvironmentVariable="SSISMeta" />
     <ConfigurationValues>
      <!-- PropertyPath contains the name of the connection manager -->
      <!-- You can leave the value property empty -->
      <ConfigurationValue
       DataType="String"
       Name="ConnectrionStringMeta"
       PropertyPath="\Package.Connections[Meta].Properties[ConnectionString]"
       Value="" />
     </ConfigurationValues>
    </PackageConfiguration>

    <!-- SQL Server Configuration -->
    <!-- The configuration table should already contain values -->

    <!-- ConnectionName is the name of the connection manager containing the configuration table -->
    <!-- Name is for both the Configuration Filter in the database table and the name in the Package Configurations Organizer window -->
    <PackageConfiguration
     ConnectionName="Meta"
     Name="SourceConfiguration">
     <!-- Table contains the name of the configuration table -->
     <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
    </PackageConfiguration>
    
   </PackageConfigurations>

   <Tasks>
    <!-- Dummy Tasks with connection to make sure the connection manager is added to the package -->
    <ExecuteSQL
     Name="SQL - Dummy 1"
     ConnectionName="Meta"
     ResultSet="None">
     <DirectInput>
      SELECT @@VERSION AS 'SQL Server Version'
     </DirectInput>
    </ExecuteSQL>
    <ExecuteSQL
     Name="SQL - Dummy 2"
     ConnectionName="Source"
     ResultSet="None">
     <DirectInput>
      SELECT @@VERSION AS 'SQL Server Version'
     </DirectInput>
    </ExecuteSQL>
   </Tasks>
  </Package>
 </Packages>
</Biml>

Download
The combination of these two configuration types is often used in a DTAP street.

Parent Package Variable Configuration
I have a variable that is filled by a variable from the parent package. This is done with Parent Package Variable Configuration. In BIML script you will find this in the variable tag and not in the configurations tag!

Parent Package Variable Config

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

   <!-- Parent Package Variable Configuration -->
   <!-- Note: this is not in the Configurations tag, but in the variable tag -->

   <!-- InheritFromPackageParentConfigurationString is for both the name of the parent package variable -->
   <!-- and the name in the Package Configurations Organizer window-->
   <Variables>
    <Variable
     DataType="String"
     Name="MyChildPackageVariable"
     InheritFromPackageParentConfigurationString="MyParentPackageVariable"
     Namespace="User">SSISJoost</Variable>
   </Variables>

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

Download

XML Configuration File
I have a Connection Manager and I have an XML configuration file to configure its connectionstring. The xml/dtsConfig file already exists with the correct values otherwise the package won't work.

XML Configuration File

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 <Connections>
  <OleDbConnection
   Name="Destination"
   ConnectionString="Data Source=.;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
  </OleDbConnection>
 </Connections>
 <Packages>
  <Package Name="Child01" ConstraintMode="Linear">

   <PackageConfigurations>
    <!-- XML Configuration File -->

    <!-- The name is for the name in the Package Configurations Organizer window-->
    <PackageConfiguration Name="Destination Configuration">
     <!-- ExternalFilePath is the path of the config file  -->
     <ExternalFileInput
      ExternalFilePath="D:\DestinationConfigurations.dtsConfig">
     </ExternalFileInput>
     <ConfigurationValues>
      <!-- You can leave the value property empty -->
      <!-- The value of the PropertyPath should also be in the DtsConfig file -->
      <ConfigurationValue
       DataType="String"
       Name="ConnectionStringDestination"
       PropertyPath="\Package.Connections[Destination].Properties[ConnectionString]"
       Value=""
       >
      </ConfigurationValue>
     </ConfigurationValues>
    </PackageConfiguration>

   </PackageConfigurations>

   <Tasks>
    <!-- Dummy Tasks with connection to make sure the connection manager is added to the package -->
    <ExecuteSQL
     Name="SQL - Dummy"
     ConnectionName="Destination"
     ResultSet="None">
     <DirectInput>
      SELECT @@VERSION AS 'SQL Server Version'
     </DirectInput>
    </ExecuteSQL>
   </Tasks>
  </Package>
 </Packages>
</Biml>

Download
This XML configuration type can also be used in a DTAP street.