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 BIDS Helper.

This feature is available in BIDS Helper 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.

 

Last edited Apr 11, 2013 at 3:17 PM by furmangg, version 1