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>
DownloadSQL 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>
DownloadThe 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>
DownloadXML 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>
DownloadThis XML configuration type can also be used in a DTAP street.
