Adding Connection Managers to a Metadata Driven Package Generator

Where This Fits

A metadata driven generator that already builds empty SSIS packages from a Package table is a good starting point. The next layer is connection managers. Both the source and target need OleDb connections, the package needs to reference them, and the generated packages need stable identifiers so multiple developers do not produce conflicting GUIDs.

This walkthrough adds two connections to the metadata, wires them into the generated packages, and addresses two pitfalls along the way.

Source and Target Sample

The source can be any sample database. The example uses a table called Customer with a small set of columns in a database called SourceCatalog and an empty target database called StagingCatalog.

CREATE DATABASE StagingCatalog;
GO
USE StagingCatalog;
GO
CREATE TABLE dbo.Customer (
  CustomerKey int NOT NULL CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED,
  CustomerType nchar(2) NOT NULL,
  GivenName nvarchar(50) NOT NULL,
  FamilyName nvarchar(50) NOT NULL,
  ContactEmail nvarchar(200) NULL,
  ModifiedDate datetime NOT NULL CONSTRAINT DF_Customer_Modified DEFAULT (getdate())
);
GO

Build OleDb connection strings for each database, then load them into the metadata.

INSERT INTO Biml.ConnectionType (ConnectionTypeName) VALUES ('OleDb');

INSERT INTO Biml.Connection (ConnectionName, CreateInProject, ConnectionTypeId,
    OLEDB_ConnectionString, OLEDB_DatabaseName, ConnectionGuid)
VALUES ('SrcCustomerOleDb', 1,
    (SELECT ConnectionTypeId FROM Biml.ConnectionType WHERE ConnectionTypeName = 'OleDb'),
    'Data Source=.;Initial Catalog=SourceCatalog;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;',
    'SourceCatalog', NEWID());

INSERT INTO Biml.Connection (ConnectionName, CreateInProject, ConnectionTypeId,
    OLEDB_ConnectionString, OLEDB_DatabaseName, ConnectionGuid)
VALUES ('TgtStagingOleDb', 1,
    (SELECT ConnectionTypeId FROM Biml.ConnectionType WHERE ConnectionTypeName = 'OleDb'),
    'Data Source=.;Initial Catalog=StagingCatalog;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;',
    'StagingCatalog', NEWID());

A PackageConnection table links each Package row to the connections it needs and records whether each is the source or target of the package.

INSERT INTO Biml.PackageConnection (PackageId, ConnectionId,
    IsSource, IsTarget, TableSchema, TableName, DirectInputSql)
VALUES ((SELECT PackageId FROM Biml.Package WHERE PackageName = 'LoadCustomer'),
        (SELECT ConnectionId FROM Biml.Connection WHERE ConnectionName = 'SrcCustomerOleDb'),
        1, 0, 'dbo', 'Customer', NULL);

INSERT INTO Biml.PackageConnection (PackageId, ConnectionId,
    IsSource, IsTarget, TableSchema, TableName, DirectInputSql)
VALUES ((SELECT PackageId FROM Biml.Package WHERE PackageName = 'LoadCustomer'),
        (SELECT ConnectionId FROM Biml.Connection WHERE ConnectionName = 'TgtStagingOleDb'),
        0, 1, 'dbo', 'Customer', NULL);

Root Level Connections from Metadata

In the generator, the Connections block reads from Biml.Connection and emits one Connection element per row. Wrap the boolean CreateInProject in a small helper because Visual Studio expects lowercase true and false rather than the True and False that .NET produces by default.

<#@ template language="C#" hostspecific="true" tier="0" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Data" #>
<#@ include file="-helpers.biml" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <# var metaConn = (AstDbConnectionNode)RootNode.Connections["MetadataConn"]; #>

  <Connections>
    <# var connRows = ExternalDataAccess.GetDataTable(metaConn.ConnectionString,
        "SELECT ConnectionId, ConnectionName, OLEDB_ConnectionString, CreateInProject FROM Biml.Connection WHERE ConnectionTypeId = 1 ORDER BY ConnectionName");
       foreach (DataRow row in connRows.Rows) { #>
      <Connection Name="<#= row["ConnectionName"] #>"
                  ConnectionString="<#= row["OLEDB_ConnectionString"] #>"
                  CreateInProject="<#= LowerBool((bool)row["CreateInProject"]) #>"
                  CreatePackageConfiguration="false"
                  RetainSameConnection="true" />
    <# } #>
  </Connections>

The helper file is a tiny include that exposes a single LowerBool method.

<#+
string LowerBool(object value)
{
  if (value == null) return "false";
  return ((bool)value) ? "true" : "false";
}
#>

Per Package Connection References

Inside the Package loop, run a second query that joins PackageConnection back to Connection and emit a reference for each row. Carry the ConnectionGuid through to the Id attribute so every developer regenerates the same identifiers.

  <Packages>
    <# var pkgRows = ExternalDataAccess.GetDataTable(metaConn.ConnectionString,
        "SELECT PackageId, PackageName, ConstraintMode, ProtectionLevel FROM Biml.Package WHERE GeneratePackage = 1 ORDER BY PackageName");
       foreach (DataRow pkg in pkgRows.Rows) { #>
      <Package Name="<#= pkg["PackageName"] #>"
               ConstraintMode="<#= pkg["ConstraintMode"] #>"
               ProtectionLevel="<#= pkg["ProtectionLevel"] #>">
        <Connections>
          <# var pkgConnRows = ExternalDataAccess.GetDataTable(metaConn.ConnectionString,
              "SELECT mbc.ConnectionName, mbc.ConnectionGuid"
              + " FROM Biml.Connection mbc"
              + " INNER JOIN Biml.PackageConnection mbpc ON mbc.ConnectionId = mbpc.ConnectionId"
              + " WHERE mbpc.PackageId = " + pkg["PackageId"].ToString()
              + " ORDER BY mbc.ConnectionTypeId");
             foreach (DataRow pkgConn in pkgConnRows.Rows) { #>
            <Connection ConnectionName="<#= pkgConn["ConnectionName"] #>"
                        Id="<#= pkgConn["ConnectionGuid"] #>" />
          <# } #>
        </Connections>
        <Tasks>
          <Container Name="SEQC Main" ConstraintMode="Linear">
            <Tasks>
              <Expression Name="Placeholder" Expression="1" />
            </Tasks>
          </Container>
        </Tasks>
      </Package>
    <# } #>
  </Packages>
</Biml>

Two Pitfalls Worth Knowing

Boolean casing. The metadata column for CreateInProject is a bit, which Biml exposes as a System.Boolean. The default ToString produces True or False, but the SSIS attribute requires lowercase. The LowerBool helper above bridges that gap.
Connection manager GUIDs. Without the Id attribute on the per package Connection element, every developer's generated package gets a fresh GUID. Carrying the GUID through from the metadata keeps the identifiers stable across machines. Once the Id is in place, regenerate packages but uncheck the connection managers in the BimlExpress confirmation dialog so existing connection manager files are not rewritten.

Generating and Verifying

Select the metadata connection file and the package generator together, then choose Check Biml for Errors. After fixing any reported issues, run Generate SSIS Packages. Each generated package now has the two connection managers attached, and the connection manager GUIDs match the values stored in metadata.

Where This Fits​

Source and Target Sample​

Root Level Connections from Metadata​

Per Package Connection References​

Two Pitfalls Worth Knowing​

Generating and Verifying​