Monday 30 November 2015

BIML force creating connection managers

Case
If you declare a connection manager in BIML, but don't use it in one of the tasks or transformations, it won't be created. Can you force BIML to create the connection managers nevertheless?


No connection managers were created
















Solution
In some cases you want to override this feature and just create the connection managers. For example when using Custom Tasks/Transformations where BIML doesn't recognize a connection manager attribute.



To force BIML to create the connection managers you need to add a second <Connections> tag, but this time within the package tag. And within this tag you can add <Connection> tags with a ConnectionName attribute. As a value you need to need to supply the name of the connection manager that you created in the first <Connections> tag.
Force creating connection managers
















<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <AdoNetConnection ConnectionString="Data Source=.;Initial Catalog=tempdb;Integrated Security=True;"
        Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        Name="myStage"
        CreateInProject="true"
        />
    <FileConnection FileUsageType="ExistingFolder"
        FilePath="d:\"
        Name="myFolder"
        CreateInProject="false"
        />
  </Connections>

  <Packages>
    <Package Name="myPackage" ProtectionLevel="DontSaveSensitive">
      <Tasks>
        <Container Name="myContainer">
          
        </Container>
      </Tasks>
      <Connections>
        <!--  Force creating connection managers  -->
        <Connection ConnectionName="myStage" />
        <Connection ConnectionName="myFolder" />
      </Connections>
    </Package>
  </Packages>
</Biml>


You can even determine the guid of each connection manager.
<Connections>
  <!--  Force creating connection managers  -->
  <Connection ConnectionName="myStage"
        Id="{365878DA-0DE4-4F93-825D-D8985E2765FA}"/>
  <Connection ConnectionName="myFolder"
        Id="{365878DA-0DE4-4F93-825D-D8985E2765FB}"/>
</Connections>


And if you need the same GUID in multiple places within your script, but you want a random GUID, then you can add a string variable and fill it with a random GUID. Then you can use that variable in multiple places.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <AdoNetConnection ConnectionString="Data Source=.;Initial Catalog=tempdb;Integrated Security=True;"
            Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
            Name="myStage"
            CreateInProject="true"
        />
    <FileConnection FileUsageType="ExistingFolder"
            FilePath="d:\"
            Name="myFolder"
            CreateInProject="false"
        />
  </Connections>
  
  <#
    // Create Random Guid but use it in multiple places
    string myGuid = System.Guid.NewGuid().ToString();
  #>

    <Packages>
    <Package Name="myPackage" ProtectionLevel="DontSaveSensitive">
      <Tasks>
        <Container Name="myContainer">
          
        </Container>
      </Tasks>
      <Connections>
        <!--  Force creating connection managers    -->
        <Connection ConnectionName="myStage"
              Id="<#=myGuid#>"/>
        <Connection ConnectionName="myFolder"
              Id="{365878DA-0DE4-4F93-825D-D8985E2765FB}"/>
      </Connections>
    </Package>
  </Packages>
</Biml>
Related Posts Plugin for WordPress, Blogger...