SQL Saturday!!!

Yay!!! I’ll be speaking about Biml at SQL Saturday in Chattanooga in June!
I’m very excited. In typical fashion, I will probably cram too much into the hour, but I’ll try not to. Here’s the abstract for my session.

SQL Saturday

Using Biml to Automate the Generation of SSIS Packages

So, you need to move data from 75 tables on the mainframe to new tables in SQL Azure. Do you: a) hand code one package to load all 75 tables, b) hand code 75 packages that move a table each, or c) wish there was a better way?
There is! Business Intelligence Markup Language (Biml) can automate the creation of packages so that they all follow the same script. In this session, we will create some simple metadata to be able to generate multiple packages and their associated connection managers. You will see Biml in action. You will see the XML that Biml uses to define packages and connections. You will see the C# code that Biml uses to fetch metadata and dynamically generate packages in SSIS. And you will see packages and connection managers generated from Biml before your very eyes.

Biml Tutorial Part 3 – Adding Connection Managers to our Package Generator

Now that we can generate simple empty packages, the next step is to add connection managers to the project and package.  After that, we will add a DataFlow.  This article will focus on the Connections though, and we will start with a pair of standard OleDb Connections.  Recall the metadata tables from the last article:

Setting up the Source and Target Databases

To insert the connection information, we’ll need to add a source, a target, the type (which is Ole Db) and then we’ll need to hook them up to the Package.
For the source, I’ll use the AdventureWorks2014 Database for SQL Server 2014.  Any DB will do though.
For the target, I’ve created an empty database called MySqlDatabase and added the Person table (from AdventureWorks2014) with the following SQL:

CREATE DATABASE MySqlDatabase;
GO
USE MySqlDatabase;
GO
CREATE TABLE dbo.[Person](
	[BusinessEntityID] [int] NOT NULL CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY CLUSTERED,
	[PersonType] [nchar](2) NOT NULL,
	[NameStyle] bit NOT NULL CONSTRAINT [DF_Person_NameStyle] DEFAULT 0,
	[Title] [nvarchar](8) NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[Suffix] [nvarchar](10) NULL,
	[EmailPromotion] [int] NOT NULL CONSTRAINT [DF_Person_EmailPromotion] DEFAULT 0,
	[AdditionalContactInfo] [xml] NULL,
	[Demographics] [xml] NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Person_rowguid] DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Person_ModifiedDate] DEFAULT (getdate()))
GO

We need to fashion an OleDb Connection String for each database, like so:

Source:  Data Source=.;Initial Catalog=AdventureWorks2014;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;
Target:  Data Source=.;Initial Catalog=MySqlDatabase;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

Then we can insert the connection records, starting by adding a Connection Type of OleDb.

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

Now that we have the connection strings in the database, we need to hook them up to the package.  In the PackageConnection table, we also need to provide the TableSchema and TableName or SQL Statement.  In this case, we’ll pull from Person.Person.  Here are the SQL statements to insert the required rows into the PackageConnection table:

INSERT INTO Biml.PackageConnection (PackageId, ConnectionId,
    IsSource, IsTarget, TableSchema, TableName, DirectInputSql)
VALUES ((SELECT PackageId FROM Biml.Package WHERE PackageName = 'My First Biml Package'),
    (SELECT c.ConnectionId FROM Biml.Connection c WHERE c.ConnectionName = 'My First Source'),
    1,0,'Person','Person',NULL);
INSERT INTO Biml.PackageConnection (PackageId, ConnectionId,
    IsSource, IsTarget, TableSchema, TableName, DirectInputSql)
VALUES ((SELECT PackageId FROM Biml.Package WHERE PackageName = 'My First Biml Package'),
    (SELECT c.ConnectionId FROM Biml.Connection c WHERE c.ConnectionName = 'My First Target'),
    0,1,'dbo','Person',NULL);

Configuring the Biml file for Connections

Because we are using the OLEDB drivers, and have tables in the source and target that have the same structure, we do not need to map the fields.  Biml and SSIS will automatically map those.
So, we have populated the metadata.  Now, we need to modify the Biml so that it includes the Connection information.  In the structure of a Biml file, the connections are fully described in the root

<Connections></Connections>

section, and are then referenced inside the package

<Connections></Connections>

section.  Here’s what this would look like if we hard-coded the connections in the Biml file:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">

	<!--Root level Connections-->
    <Connections>
        <Connection Name="My First Source"
    		ConnectionString="Provider=SQLNCLI11;Server=.;Database=AdventureWorks2014;Trusted_Connection=yes;"
			CreateInProject="true"
			CreatePackageConfiguration="false" RetainSameConnection="true" />
        <Connection Name="My First Target"
    		ConnectionString="Provider=SQLNCLI11;Server=.;Database=MySqlDatabase;Trusted_Connection=yes;"
			CreateInProject="true"
			CreatePackageConfiguration="false" RetainSameConnection="true" />
    </Connections>

    <Packages>
        <Package Name="My First Biml Package" ConstraintMode="Parallel"
			ProtectionLevel="EncryptSensitiveWithUserKey">
			
			<!--Package level Connection references-->
            <Connections>
                <Connection ConnectionName="My First Source" />
                <Connection ConnectionName="My First Target" />
             </Connections>
             <Tasks>
                 <Container Name="SEQC - Main Control Flow" ConstraintMode="Linear">
                     <Variables></Variables>
                     <Tasks>
                         <Expression Name="Placeholder" Expression="1" />
                     </Tasks>
                 </Container>
             </Tasks>
        </Package>
    </Packages>
</Biml>

Now that we’ve looked at what the Biml code would look like if we hard coded everything, let’s get back to the version of the code we had at the end of the last article:

<#@ template language="C#" hostspecific="true" tier="0"#>
<#@ import namespace="System" #>
<#@ import namespace="System.Data" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <# var bimlConfigConnection = (AstDbConnectionNode)RootNode.Connections["MainBimlConnection"]; #>
    <Packages>
        <# var packagesToCreate = ExternalDataAccess.GetDataTable(bimlConfigConnection.ConnectionString, 
        "SELECT PackageId, PackageName, ConstraintMode, ProtectionLevel, GeneratePackage FROM Biml.Package p "
        + " WHERE GeneratePackage = 1 ORDER BY p.PackageName"); 
        foreach(DataRow package in packagesToCreate.Rows) { 
        #>
            <!-- Build Package Here -->
            <Package Name="<#=package["PackageName"] #>" ConstraintMode="<#=package["ConstraintMode"] #>" 
                     ProtectionLevel="<#=package["ProtectionLevel"] #>">
                <Tasks>
    		    <Container Name="SEQC - Main Control Flow" ConstraintMode="Linear">
                        <Variables></Variables>
            	        <Tasks>
            	            <Expression Name="Placeholder" Expression="1" />
                        </Tasks>
                    </Container> 
                </Tasks>
            </Package>
        <# } #>
    </Packages>
</Biml>

Adding the Dynamic Connections to the Package

Root Level Connections

With this as the starting point, we’ll need to add a SQL Statement and a foreach() loop to pull in the connection info at the root level, like so:

<#@ template language="C#" hostspecific="true" tier="0"#>
<#@ import namespace="System" #>
<#@ import namespace="System.Data" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <# var bimlConfigConnection = (AstDbConnectionNode)RootNode.Connections["MainBimlConnection"]; #>
    <Connections>
	<# var connectionsToCreate = ExternalDataAccess.GetDataTable(bimlConfigConnection.ConnectionString, 
        "SELECT ConnectionId, ConnectionName, OLEDB_ConnectionString, CreateInProject FROM Biml.Connection Where ConnectionTypeId = 1 order by ConnectionName"); 
        foreach(DataRow connection in connectionsToCreate.Rows) { #>
           <Connection Name="<#=connection["ConnectionName"] #>" ConnectionString="<#=connection["OLEDB_ConnectionString"] #>" 
		   CreateInProject="<#=connection["CreateInProject"] #>" CreatePackageConfiguration="false" 
		   RetainSameConnection="true" />
        <# } #>
    </Connections> 

Package Level Connection References

We also a need the SQL Statement and foreach() loop to pull in the connection info at the package level. Here we’ll use the PackageId from the Package loop as a criterion in the WHERE clause:

<Package Name="<#=package["PackageName"] #>" ConstraintMode="<#=package["ConstraintMode"] #>" 
    ProtectionLevel="<#=package["ProtectionLevel"] #>">
    <Connections>
        <# connectionsToCreate = ExternalDataAccess.GetDataTable(bimlConfigConnection,
            "SELECT mbc.ConnectionName, CreateInProject, ConnectionGuid "
            + " FROM Biml.Connection mbc"
            + " INNER JOIN Biml.PackageConnection mbpc ON mbc.ConnectionId = mbpc.ConnectionId"
            + " INNER JOIN Biml.Package mbp ON mbpc.PackageId = mbp.PackageId"
            + " WHERE mbp.GeneratePackage = 1 and mbpc.PackageId = " + package["PackageId"].ToString()
            + " ORDER BY mbc.ConnectionTypeId;");
        foreach(DataRow connection in connectionsToCreate.Rows) { 
        #>
            <Connection ConnectionName="<#=connection["ConnectionName"] #>" />
        <# } #>
    </Connections>

Gotcha! #1

Now, here’s a nice little Gotcha! In Sql Server, in the Biml.Connection table, we store the CreateInProject flag as a bit. Biml rightly converts this to a bool, with values of True or False. However, the CreateInProject attribute requires values of true or false, with lowercase first letters. In order to correct this behavior, we need to add a function that can convert the bool values into a string that is either “true” or “false”. This is accomplished by adding a new Biml file, let’s call it -code.biml, and then creating a function in that file:

<#+ 
string FixBool(object inBool)
{
	
    if (inBool == "False" || inBool == "0")
    {
		return "false";
    }
    else
    {
        return "true";
    }
}
#>

Notice the symbols at the top and bottom of the function. These allow Biml to use this function as an include file. In the main biml file, we need to add a line:

<#@ include file="-code.biml" #>

And now we can wrap the CreateInProject flag inside the FixBool() function, like so:

   foreach(DataRow connection in connectionsToCreate.Rows) { #>
		<Connection Name="<#=connection["ConnectionName"] #>" ConnectionString="<#=connection["OLEDB_ConnectionString"] #>" 
		CreateInProject="<#=FixBool((bool)connection["CreateInProject"]) #>" 
		CreatePackageConfiguration="false" RetainSameConnection="true" />
	<# } #>
</Connections>

Testing it all: Generating the package.

Let’s test it all.

  1. Shift-click to select the MainBimlConnection.biml file and the My Second Package.biml file.
  2. Right-click and Check Biml for Errors. Fix any errors.
  3. Right-click and Generate SSIS Packages.

If everything went well, you should now have two new Package Level connection managers and a new SSIS Package.
So, it looks like everything worked.

Gotcha! #2

There is one last Gotcha! though. This one has to do with the Guids of the Connection Managers. If three different developers generate the packages you’ve defined here, they will get three different Guids for each connection manager.
To fix this, we need everyone to use connection managers with the same Guid.
Which Guid should it be? It should be the one in the Connection table.
How do I change it? That field is grayed out. Open the connection manager in Code view instead of Design view and you can paste in the Guid that was used in the Connection table.
Changing the Guid for a Connection Manager
Once you’ve changed the connection manager’s Guid to match the one in the Connection table, we need to tell Biml to use that Guid when it’s generating the package, like so:

        foreach(DataRow connection in connectionsToCreate.Rows) { 
        #>
            <Connection ConnectionName="<#=connection["ConnectionName"] #>" Id="<#=connection["ConnectionGuid"] #>" />
        <# } #>
    </Connections>

Then regenerate the packages. Don’t let it regenerate the connection managers though. You can prevent this by unchecking the connection managers in the confirmation box:
Biml Confirmation Dialog
As you increase the number of packages, you will get a lot more connection managers generated. The easiest way I have found to not let it regenerate them, is to check the ones you want to keep into Source Code Management (TFS, Git, etc.), then let Biml generate new connection managers and then Undo the Pending changes, so that you revert to the saved and checked-in version of the connection managers.

In Conclusion

Putting it all together, we get this Biml file:

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

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <# var bimlConfigConnection = (AstDbConnectionNode)RootNode.Connections["MainBimlConnection"]; #>
    
	<Connections>
	    <# var connectionsToCreate = ExternalDataAccess.GetDataTable(bimlConfigConnection.ConnectionString, 
        "SELECT ConnectionId, ConnectionName, OLEDB_ConnectionString, CreateInProject FROM Biml.Connection Where ConnectionTypeId = 1 order by ConnectionName"); 
       foreach(DataRow connection in connectionsToCreate.Rows) { #>
            <Connection Name="<#=connection["ConnectionName"] #>" ConnectionString="<#=connection["OLEDB_ConnectionString"] #>" 
            CreateInProject="<#=FixBool((bool)connection["CreateInProject"]) #>" 
            CreatePackageConfiguration="false" RetainSameConnection="true" />
		<# } #>
    </Connections>    
    
    <Packages>
        
        <# var packagesToCreate = ExternalDataAccess.GetDataTable(bimlConfigConnection.ConnectionString, 
        "SELECT PackageId, PackageName, ConstraintMode, ProtectionLevel, GeneratePackage FROM Biml.Package p "
        + " WHERE GeneratePackage = 1 ORDER BY p.PackageName"); 
       foreach(DataRow package in packagesToCreate.Rows) { 
            #>
            
        	<!-- Build Package Here -->
        	<Package Name="<#=package["PackageName"] #>" ConstraintMode="<#=package["ConstraintMode"] #>" 
        	        ProtectionLevel="<#=package["ProtectionLevel"] #>">
        	        
    	        <Connections>
            		<# connectionsToCreate = ExternalDataAccess.GetDataTable(bimlConfigConnection,
            		   "SELECT mbc.ConnectionName, CreateInProject, ConnectionGuid "
            			+ " FROM Biml.Connection mbc"
            			+ " INNER JOIN Biml.PackageConnection mbpc ON mbc.ConnectionId = mbpc.ConnectionId"
            			+ " INNER JOIN Biml.Package mbp ON mbpc.PackageId = mbp.PackageId"
            			+ " WHERE mbp.GeneratePackage = 1 and mbpc.PackageId = " + package["PackageId"].ToString()
            			+ " ORDER BY mbc.ConnectionTypeId;");
                   foreach(DataRow connection in connectionsToCreate.Rows) { 
            		   #>
                    <Connection ConnectionName="<#=connection["ConnectionName"] #>" Id="<#=connection["ConnectionGuid"] #>" />
            		<# } #>
                 </Connections>
        	        
                <Tasks>
            		<Container Name="SEQC - Main Control Flow" ConstraintMode="Linear">
            			<Variables></Variables>
            			<Tasks>
            				<Expression Name="Placeholder" Expression="1" />
            			</Tasks>
                    </Container> 
                </Tasks>
            </Package>
        <# } #>
    </Packages>
</Biml>

And it will generate this package (notice the Connection Managers added at the bottom):
Generated Package - After Adding Connections

endmark endmark endmark