Using .NET Variables to Partially Automate BimlScript

gravatar

Scott Currie

Learn how to define Biml templates and use simple variable replacement to partially automate Biml code.

published 08.14.15

last updated 08.21.15


Part of lesson Using .NET Variables to Partially Automate BimlScript.

Share

Introduction

It may have felt like we dove directly into the deep end of the pool in the previous lesson where you learned about the different types of BimlScript code nuggets. In this lesson, we're going to slow down, take a step back, and see how to use those code nuggets in a simple and practical way. Specifically, we will avoid discussion of heavy automation and instead focus on the mechanics how code nuggets are capable of setting variable values and then using those variables later on.

The Scenario

Imagine that you need to extract all of the data from a table on a source system and then load a copy of that data into a table with matching schema on a target system. Since the servers aren't linked, SSIS is a great choice for doing this. In fact, if I were doing it for a single table, it would be fast and easy - either with the BIDS/SSDT design tools or with Biml. What if we need to do it for 30 tables? Now it starts to become very tedious with BIDS/SSDT. With plain Biml code (i.e. no code nuggets), it's slightly less tedious and more manageable, but still not something I'd be eager to do.

In the following sections, we'll see how we can set up a Biml file that makes it much quicker to generate those 30 packages by setting a single variable, building, changing the variable, rebuilding, etc. By taking this approach, we reduce the time to create each package from perhaps 15 minutes to at most 15 seconds.

In the next lesson, we'll see how we can add a bit more C# code to remove the manual variable modification. That will result in perhaps 15 seconds to build all tables (rather than 15 seconds each). But we're going to take baby steps. Please bear with me.

The Template

Whenever you think about automating your Biml code, first thing about creating a template that describes the pattern you want to reuse. You will usually have the most success by creating an actual working Biml code sample. Here's what the Biml template for our scenario would look like:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Source" ConnectionString="Provider=SQLNCLI11;Server=SOURCE_SERVER;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;" />
        <OleDbConnection Name="Target" ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;" />
    </Connections>
    <Packages>
        <Package Name="Copy DimAccount" ConstraintMode="Parallel">
            <Tasks>
                <Dataflow Name="DFT_CopyData">
                    <Transformations>
                        <OleDbSource Name="Retrieve Rows" ConnectionName="Source">
                            <DirectInput>SELECT * FROM DimAccount</DirectInput>
                        </OleDbSource>
                        <OleDbDestination Name="Load Rows" ConnectionName="Target">
                            <ExternalTableOutput Table="DimAccount" />
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

(I know that using SELECT * is an anti-pattern. We'll fix that in later lessons, but for now it helps to keep our code simple.)

If you build that Biml code, you will get a package that copies data from DimAccount on a source server to DimAccount on a target server. So we have a working Biml template. What next?

Identifying Values to be Replaced

To add our code nuggets, we need to identify the places in the code that will change for each of our tables. In our scenario, we need to modify the following for each table:

  1. The name of the package must change so that all of the packages can coexist.
  2. The table name in the SELECT * statement in the OleDbSource must change to reflect the source table name.
  3. The table name in the OleDbDestination must change to reflect the target table name.

Replacing Values with Code Nuggets

Now that we know what needs to be changed for each table, let's replace those values with code nuggets. To do this, we begin by defining a variable that will hold the table name. Then we use that variable wherever the table name would have been used:

<# var tableName = "DimAccount"; #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Source" ConnectionString="Provider=SQLNCLI11;Server=SOURCE_SERVER;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;" />
        <OleDbConnection Name="Target" ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;" />
    </Connections>
    <Packages>
        <Package Name="Copy <#=tableName#>" ConstraintMode="Parallel">
            <Tasks>
                <Dataflow Name="DFT_CopyData">
                    <Transformations>
                        <OleDbSource Name="Retrieve Rows" ConnectionName="Source">
                            <DirectInput>SELECT * FROM <#=tableName#></DirectInput>
                        </OleDbSource>
                        <OleDbDestination Name="Load Rows" ConnectionName="Target">
                            <ExternalTableOutput Table="<#=tableName#>" />
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

How about that? Now we can change that one variable value at the top of the file, click to rebuild, and get a customized package for that table. With just a few BimlScript code nuggets, we have "semi-automated" our package generation. And now, as promised, we only need to spend seconds per package rather than minutes per package.

Conclusion

By using simple variable replacement, we've already saved a tremendous amount of time when generating packages for our repeatable table copy pattern. In the next lesson, we'll see how to take this even further with full BimlScript automation.

Finished?

Complete the lesson Using .NET Variables to Partially Automate BimlScript:

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

There are no comments yet.