I have been reading some articles about BIML. And now I wanted to test it myself. In a new project I am starting with I have to move a lot of tables from source to staging. A perfect job for BIML.

Please note that copying the BIML code is not that easy. When pasting it to you own file it will change some of the markup. And your file will not compile. Please compare your copied code against the original code.

First of all I downloaded and installed the latest version of BIDS Helper. Restart Data Tools.

Then I created 2 new databases. One called “Meta” that should keep track of all tables I want to copy from source to staging. And one called STG. In the “Meta” database I will make a table called “MyStagingTables”.

CREATE DATABASE [Meta]

USE [Meta]

CREATE TABLE [dbo].[MyStagingTables](
	[TableName] [varchar](250) NULL
) ON [PRIMARY]

-- Add some values (these are some of the tables in the AdventureWorksDW2012 database)

INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'DimDate')
INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'FactInternetSales')
INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'DimPromotion')
INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'DimSalesReason')
INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'FactSurveyResponse')
INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'FactResellerSales')
INSERT [dbo].[MyStagingTables] ([TableName]) VALUES (N'FactCallCenter')

CREATE DATABASE [stg]

Then I want to script all of these tables for creating them in my staging database. I am sure this can be done with BIML. But I used SQL 🙂

use [AdventureWorksDW2012]

Declare @createStatement varchar(max)
Declare curP cursor For

select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')
AND tc.TABLE_NAME in (select TableName from Meta.dbo.[MyStagingTables])

OPEN curP 
Fetch Next From curP Into @createStatement

While @@Fetch_Status = 0 Begin

print @createStatement

Fetch Next From curP Into @createStatement

End -- End of Fetch

Close curP
Deallocate curP

If you run this SQL it will print all the create table statements for you. Copy these and execute it on the stg database.

Then I want to add a new column called “ExecutionDate” to all of these tables. Run the SQL below, copy the output and then execute it on the stg database.

Declare @alterStatement varchar(max)
Declare curP cursor For

SELECT TABLENAME from Meta.dbo.MyStagingTables

OPEN curP 
Fetch Next From curP Into @alterStatement

While @@Fetch_Status = 0 Begin

print 'alter table ' + @alterStatement + ' add ExecutionDate datetime'

Fetch Next From curP Into @alterStatement

End -- End of Fetch

Close curP
Deallocate curP

You should now have all your tables in the stg database. And all of these tables should have a column called “ExecutionDate”.

Lets open Data Tools and create a new SSIS Project called “BimlTest”. Right click on the project and add 2 new Biml files. One called “CreateSTGPackages.biml” and one called “CreateMasterPackage.biml”.

In the file called “CreateSTGPackages.biml” copy this code:

<#@ import namespace="System.Data" #>
	<#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>

		<Biml xmlns="http://schemas.varigence.com/biml.xsd">
			<Connections>
				<Connection Name="sql2014" ConnectionString="Data Source=server;Initial Catalog=AdventureWorksDW2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" />
				<Connection Name="stg" ConnectionString="Data Source=server;Initial Catalog=STG;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" />
			</Connections>

			<Annotations>
					<Annotation>
						Moving table from source to stage.
						Package generated from BIML
					</Annotation>
				</Annotations>

			<Packages>
				<# string sConn = "Data Source=server;Initial Catalog=Meta;Provider=SQLNCLI11.1;Integrated Security=SSPI;"; #>
				<# string sSQL = "SELECT TABLENAME FROM MyStagingTables" ; #>
				<# DataTable tblPackages = ExternalDataAccess.GetDataTable(sConn,sSQL); #>
				<# foreach (DataRow pkg in tblPackages.Rows) {#>
				<Package Name="STG_<#=pkg["TABLENAME"]#>" ConstraintMode="Linear">
					<Tasks>

					<ExecuteSQL Name="Truncate table STG_<#=pkg["TABLENAME"]#>" ConnectionName="stg" ResultSet="None">
						<DirectInput>truncate table <#=pkg["TABLENAME"]#></DirectInput>
					</ExecuteSQL>

						<# string sSQLColumns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + pkg["TABLENAME"] + "'" ; #>
						<Dataflow Name="Moving data from source to staging">

						<Transformations>
						<OleDbSource Name="<#=pkg["TABLENAME"]#>" ConnectionName="sql2014">
							<DirectInput>SELECT * FROM <#=pkg["TABLENAME"]#></DirectInput>
						</OleDbSource>

						 <DerivedColumns Name="Adding Metadata">
                            <Columns>
                                <Column Name="ExecutionDate" DataType="DateTime">GETDATE()</Column>
                            </Columns>
                        </DerivedColumns>

						<OleDbDestination Name="stg <#=pkg["TABLENAME"]#>" ConnectionName="stg">
							<ExternalTableOutput Table="<#=pkg["TABLENAME"]#>" />
						</OleDbDestination>

						</Transformations>

						</Dataflow>
					</Tasks>
				</Package>
				<# } #>

			</Packages>
		</Biml>

And in the script called “CreateMasterPackage.biml” you should copy this code

<!--Includes/Imports for C#-->
<#@ template language="C#" hostspecific="true"#>
	<#@ import namespace="System.Data"#>
		<#@ import namespace="System.Data.SqlClient"#>
			<Biml xmlns="http://schemas.varigence.com/biml.xsd">
				<Connections>
					<OleDbConnection
					Name="SSISDB"
					ConnectionString="Data Source=server;Initial Catalog=SSISDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
					</OleDbConnection>
				</Connections>
				<Packages>
					<Package Name="MyMasterPackage.dtsx" ConstraintMode="Linear" AutoCreateConfigurationsType="None" ProtectionLevel="DontSaveSensitive">
						<Tasks>
							<# string sConn = @"Provider=SQLNCLI11.1;Server=sql2014;Initial Catalog=SSISDB;Integrated Security=SSPI;Connection Timeout=0;"; #>
							<# string sSQL = "SELECT Packages.[name] FROM [SSISDB].[internal].[packages] as Packages INNER JOIN [SSISDB].[internal].[projects] as Projects ";#>
							<# sSQL += "on Packages.project_version_lsn = Projects.object_version_lsn WHERE Projects.name = 'BimlTest' AND Packages.name like 'STG_%' ORDER BY Packages.name"; #>
							<# DataTable tblPackages = ExternalDataAccess.GetDataTable(sConn,sSQL); #>
							<# foreach (DataRow row in tblPackages.Rows){ #>
							<ExecutePackage Name="EPT -<#=row["name"]#>">
								<ExternalProjectPackage Package="<#=row["name"]#>">
								</ExternalProjectPackage>
							</ExecutePackage >
							<# } #>
						</Tasks>
					</Package>
				</Packages>
			</Biml>
		

Right click you SSIS Project again and choose options.

Change protection level to match the screenshot.

Now to the fun part. Right click on “CreateSTGPackages” and choose “Generate SSIS Packages”. If you get error please fix them. If everything is okay you will get new files in your project.

The packages will consist of a SQL Task that truncates the table. And a Data Flow that copies the data.

Deploy the SSIS project to SSISDB and create a folder called BimlTest.

Then run the package “CreateMasterPackage”. This will create a master package that executes all the other packages.

Now you should be able of executing this master package and everything should be fine 🙂