Biml - Replicate-O-Matic

I while back I posted about Copy all the tables. Since then, I've done a lot more Biml and one of the problems I had was duplicated logic across lots of .biml files. I say I had the problem because I didn't know about CallBimlScript. Well, I knew about it because yeah, I can include a file but I didn't realize the power there. CallBimlScript allows you to make functions for your biml. You can define parameters to it. That changes everything in my mind. Let's look the copy all tables example but this time, let's modularize it.

We'll create two biml files. One will be inc_Package.biml and the other will be Driver.biml. The definition of Driver.Biml remains the same, we're just going to cut out the actual Package block(s).

<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<!--
<#
    string connectionStringSource = @"Server=localhost\dev2012;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11.1";
    string connectionStringDestination = @"Server=localhost\dev2012;Initial Catalog=AdventureWorksDW2012_DEST;Integrated Security=SSPI;Provider=SQLNCLI11.1";

    string SrcTableQuery =     @"
SELECT
    SCHEMA_NAME(t.schema_id) AS schemaName
,   T.name AS tableName
FROM
    sys.tables AS T
WHERE
    T.is_ms_shipped = 0
    AND T.name <> 'sysdiagrams';
";

    DataTable dt = null;
    dt = ExternalDataAccess.GetDataTable(connectionStringSource, SrcTableQuery);
#>    
-->
    <Connections>
        <OleDbConnection
            Name="SRC"
            CreateInProject="false"
            ConnectionString="<#=connectionStringSource#>"
            RetainSameConnection="false">
        </OleDbConnection>
        <OleDbConnection
            Name="DST"
            CreateInProject="false"
            ConnectionString="<#=connectionStringDestination#>"
            RetainSameConnection="false">
        </OleDbConnection>
    </Connections>

    <Packages>
        <# foreach (DataRow dr in dt.Rows) { #>
            <#=CallBimlScript("inc_Package.biml", dr[0].ToString(), dr[1].ToString())#>
        <# } #>
    </Packages>
</Biml>

So that looks a little easier to understand. We get our dataset from our external source, create the connections and for each table in our dataset, we call the inc_Package.biml file passing in columns 0 and 1, which corresponds to our schema name and table name.

Our inc_Package.biml file isn't that different either. What's crucial is the first two lines where we define our property values of schema and table. Our file expects two parameters now and then we reference them in the code as <#=schema#>, where previously we would have used <#=dr[0].ToString()#>

<#@ property name="schema" type="String" #>
<#@ property name="table" type="String" #>

            <Package ConstraintMode="Linear"
                Name="<#=schema#>_<#=table#>"

            >
            <Variables>
                <Variable Name="SchemaName" DataType="String"><#=schema#></Variable>
                <Variable Name="TableName" DataType="String"><#=table#></Variable>
                <Variable Name="QualifiedTableSchema"
                          DataType="String"
                          EvaluateAsExpression="true">"[" +  @[User::SchemaName] + "].[" +  @[User::TableName] + "]"</Variable>
            </Variables>
            <Tasks>
                <Dataflow
                    Name="DFT"
                >
                    <Transformations>
                        <OleDbSource
                            Name="OLE_SRC <#=schema#>_<#=table#>"
                            ConnectionName="SRC"
                        >
                            <TableFromVariableInput VariableName="User.QualifiedTableSchema"/>
                        </OleDbSource>
                        <OleDbDestination
                            Name="OLE_DST <#=schema#>_<#=table#>"
                            ConnectionName="DST"
                            KeepIdentity="true"
                            TableLock="true"
                            UseFastLoadIfAvailable="true"
                            KeepNulls="true"
                            >
                            <TableFromVariableOutput VariableName="User.QualifiedTableSchema" />                        
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>

            </Tasks>
            </Package>

I right click on Driver.biml and select Generate SSIS Packages and boom! many packages are created to replicate all the data between my source and destination. I can't believe this is available in BIDS Helper, aka free version, of the biml engine. I have so much code to go and simplify.

Further reading

• Callable BimlScript (Caller)
• Callable BimlScript (Callee)
• Combining BIML Files
• A Biml Script to run other Biml scripts
• Making your Biml files less complex