Note: Varigence has stopped providing Biml for BI Developer Extensions so these features are deprecated. Instead install BimlExpress.
This article is going to walk through the process of creating an SSIS 2012 package which uses a project connection manager using Biml and the Biml Package Generator feature in BI Developer Extensions.
This feature is available in BI Developer Extensions 1.6.3 and later and in SSIS 2012 projects in project deployment mode (not in legacy deployment mode). The code for this feature was generously created for the community by Davide Mauri.
For this example, copy and paste the following Biml into the a .biml document, as seen in the other examples.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection Name="OLTP" ConnectionString="Data Source=localhost;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
CreateInProject="false"/>
<OleDbConnection Name="OLTP2" ConnectionString="Data Source=localhost;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
CreateInProject="true"/>
</Connections>
<Packages>
<Package Name="Test2" ConstraintMode="Linear">
<Connections>
<Connection ConnectionName="OLTP2"/>
</Connections>
<Tasks>
<ExecuteSQL Name="ES Test A" ConnectionName="OLTP2">
<DirectInput>SELECT Test=1;</DirectInput>
</ExecuteSQL>
<ExecuteSQL Name="ES Test B" ConnectionName="OLTP">
<DirectInput>SELECT Test=1;</DirectInput>
</ExecuteSQL>
<Dataflow Name="DF Test">
<Transformations>
<OleDbSource ConnectionName="OLTP2" Name="Source">
<DirectInput>SELECT Test=1;</DirectInput>
</OleDbSource>
<OleDbDestination ConnectionName="OLTP" Name="Dest">
<ExternalTableOutput Table="dbo.Dest"></ExternalTableOutput>
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>
Note the CreateInProject=’true’ setting in red on the connection. This signals Biml to create a project level connection manager.