Biml Basics for SSIS

gravatar

Scott Currie

Biml Basics for SSIS

published 08.14.15

last updated 10.06.15


Part of lesson Biml Basics for SSIS.

Share

Introduction

In this lesson, we will walk through the language features of Biml that enable you to build SSIS packages. This will be a long article, as SSIS provides a considerable amount of functionality - and Biml supports it all.

Packages

The first and most obvious thing we need to discuss is the creation of an SSIS package. To do this, we must create a Packages collection element and place a package element within it. We can do so as follows:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Package1" />
    </Packages>
</Biml>

That's all there is to it. Build this Biml, and you will create a package called Package1. In the following sections, we will explore all of the ways we can enhance our new package with additional functionality.

Control Flow

The most basic thing we will want to add to our package is a task in the control flow. To do that, we simply add a <Tasks> collection element to the package and then supply a list of the tasks we'd like to execute. Below is a simple example where we add an ExecuteSQL task. Note that in order to configure the ExecuteSQL task, we also must add the connection against which the ExecuteSQL task will run:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Packages>
        <Package Name="Package1">
            <Tasks>
                <ExecuteSQL Name="Execute Stored Procedure" ConnectionName="Connection1">
                    <DirectInput>EXEC [dbo].[uspLogError]</DirectInput>
                </ExecuteSQL>
            </Tasks>
        </Package>
    </Packages>
</Biml>

This Biml snippet will build and run against the Microsoft AdventureWorks2012 sample database. It just scratches the surface of what we can do, though. In the next section we'll take a look at other control flow tasks.

Built-in Tasks

Biml has first-class support for all of the "built-in" control flow tasks. By that, I mean all of the tasks that appear in the SSIS toolbox when you do a fresh install of SSIS and all of those add-in tasks that are distributed by Microsoft. For reference, here is a list of the Biml task elements for each of them:

Over time, we will be adding tutorials for using each of these tasks, but in the meantime, the reference documentation should get you pointed in the right direction.

Custom Tasks

While support for the built-in tasks is sufficient to implement the vast majority of SSIS packages, sometimes you need to use custom tasks that have been implemented either by one of your developers or by a third party vendor (e.g. PragmaticWorks TaskFactory or CozyRoc SSIS+).

In those cases, you can use the Biml CustomTask syntax. To use the CustomTask syntax, you do need to have some understanding of how the CustomTask chooses to store its configuration data within its ObjectData XML element. Normally, the most convenient way of discovering the appropriate configuration information is to import a package using the desired custom task using the Mist IDE.

For an example of using CustomTask syntax with the CozyRoc SSIS+ Zip Task, take a look at: http://bimlscript.com/Snippet/Details/62

Precedence Constraints

A key feature of SSIS control flows is the ability to specify the order and conditions under which tasks execute. This is normally done in BIDS/SSDT using precedence constraints, and looks like this:

Precedence Constraints in BIDS/SSDT

As you can see, precedence constraints offer a considerable number of configuration options, all of which are supported by Biml. But before we dive into those options, let's take a look at a convenient shortcut.

Linear ConstraintMode

The most common type of precedence constraint specifies that the subsequent task should only execute if the preceding task completed successfully. Since it is also very common for people to use BimlScript automation (which you'll learn more about later) to automatically generate a large number of tasks that execute sequentially, Biml packages and containers offer a shortcut mode that you can specify with ConstraintMode="Linear". When you do that, Success precedence constraints will be automatically created among the tasks in the package or container so that they run in the order they are specified in the Biml file. Let's take a look at an example:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Package1" ConstraintMode="Linear">
            <Tasks>
                <Container Name="Parallel Container">
                    <Tasks>
                        <Dataflow Name="DF_1" />
                        <Dataflow Name="DF_2" />
                        <Dataflow Name="DF_3" />
                    </Tasks>
                </Container>
                <Container Name="Linear Container" ConstraintMode="Linear">
                    <Tasks>
                        <Dataflow Name="DF_1" />
                        <Dataflow Name="DF_2" />
                        <Dataflow Name="DF_3" />
                    </Tasks>
                </Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>

When you build this Biml code, the following package results:

Linear Package and Containers

There are a couple things to note about this output. First, note that the default is for tasks to run in parallel. Second, a linear constraint mode only applies to the immediate children of the package or container on which the option is set. Consequently, setting a linear constraint mode on a package does not cause precedence constraints to be automatically created among tasks in the child containers.

While Linear ConstraintMode is commonly used and very convenient, it doesn't give us the full control over precedence constraints that we require for all scenarios. So let's take a look at the Biml syntax that permits fine-grained control over our package control flow.

Constraint Conditions

The most simple type of precedence constraint is one that uses the completion type of the predecessor task to determine whether or not to run the successor. Specifically, we can specify that a given task should only run if its predecessor completed successfully, if its predecessor failed, or if its predecessor completed (whether successfully or in failure). Here is a Biml sample that shows all three:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Package2">
            <Tasks>
                <Dataflow Name="First" />
                <Dataflow Name="RunOnSuccess">
                    <PrecedenceConstraints LogicalType="And">
                        <Inputs>
                            <Input EvaluationOperation="Constraint" EvaluationValue="Success" OutputPathName="First.Output" />
                        </Inputs>
                    </PrecedenceConstraints>
                </Dataflow>
                <Dataflow Name="RunOnFailure">
                    <PrecedenceConstraints LogicalType="And">
                        <Inputs>
                            <Input EvaluationOperation="Constraint" EvaluationValue="Failure" OutputPathName="First.Output" />
                        </Inputs>
                    </PrecedenceConstraints>
                </Dataflow>
                <Dataflow Name="RunOnCompletion">
                    <PrecedenceConstraints LogicalType="And">
                        <Inputs>
                            <Input EvaluationOperation="Constraint" EvaluationValue="Completion" OutputPathName="First.Output" />
                        </Inputs>
                    </PrecedenceConstraints>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Here is the package that will be created when you build that Biml snippet:

Constraint Only

Let's take a quick review of the Biml code and what it generated. As you may know, it's possible to include expressions that must be satisfied for the target of the precedence con

Variables

SSIS variables provide developers with the ability to store certain values during SSIS package execution for later use. Defining these variables is exceedingly easy using Biml code. As you likely know, variables can be defined within any SSIS control flow task. These variables are the available to any tasks that are descendants of the task in which they were defined. Consequently, every task element in Biml supports a collection element called Variables, which contains all of the SSIS variables that have been defined for use in that task and its descendants. Here is an example of Biml code that defines a variable and then uses is as a parameter for an ExecuteSql task:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
        <Connections>
            <OleDbConnection Name="AdventureWorksDW" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
        </Connections>
    <Packages>
        <Package Name="Package1">
            <Variables>
                <Variable Name="Variable1" DataType="Int32">0</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL Name="ExecuteSQLTask1" ConnectionName="AdventureWorksDW">
                    <DirectInput>SELECT * FROM dbo.DimAccount WHERE AccountKey = ?</DirectInput>
                    <Parameters>
                        <Parameter Name="0" DataType="Int32" VariableName="User.Variable1" />
                    </Parameters>
                </ExecuteSQL>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Expressions

SSIS expressions are used to override specific SSIS property values with dynamically calculated expression values at run time. Given Biml's extensive support for SSIS features, it should come as no surprise that Biml also supports the specification of SSIS expression override values for all SSIS task properties. In any SSIS task, you can create an <Expressions> collection element that contains the SSIS expression overrides you wish to apply to that task. Here is an example of overriding the the SqlStatementSource property of an ExecuteSQL task using Biml:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
        <Connections>
            <OleDbConnection Name="AdventureWorksDW" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
        </Connections>
    <Packages>
        <Package Name="Package1">
            <Variables>
                <Variable Name="Variable1" DataType="Int32">0</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL Name="ExecuteSQLTask1" ConnectionName="AdventureWorksDW">
                    <DirectInput>SELECT * FROM dbo.DimAccount</DirectInput>
                    <Expressions>
                        <Expression PropertyName="SqlStatementSource">"SELECT * FROM dbo.DimAccount WHERE AccountKey = " + (DT_WSTR, 100) @[User::Variable1]</Expression>
                    </Expressions>
                </ExecuteSQL>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Note that the standard way of referencing a specific overridden SSIS task property is to use the PropertyName attribute. In some cases, you cannot use this referencing mechanism either because the property has not been configured in Biml or because the property is defined in a 3rd party custom task that does not have first class support in Biml. For such cases, you can use the external property syntax to reference any property that SSIS permits to be overridden by expressions. Below is the same example as before, but using the external property syntax:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
        <Connections>
            <OleDbConnection Name="AdventureWorksDW" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
        </Connections>
    <Packages>
        <Package Name="MySecondBimlPackage">
            <Variables>
                <Variable Name="Variable1" DataType="Int32">0</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL Name="ExecuteSQLTask1" ConnectionName="AdventureWorksDW">
                    <DirectInput>SELECT * FROM dbo.DimAccount</DirectInput>
                    <Expressions>
                        <Expression ExternalProperty="SqlStatementSource">"SELECT * FROM dbo.DimAccount WHERE AccountKey = " + (DT_WSTR, 100) @[User::Variable1]</Expression>
                    </Expressions>
                </ExecuteSQL>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Event Handlers

A powerful feature of SSIS is the ability to configure SSIS control flow fragments to run in response to events that are raised on other SSIS tasks. As you might expect, these are called Event Handlers, and they are fully supported by Biml.

To declare event handlers on a given task in Biml, you only need to add an <Events> collection element to the task and then populate it with event fragments for each of the desired event types. Here is an example of using event handlers for both the OnError and the OnPreExecute event types on a package:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
        <Connections>
            <OleDbConnection Name="AdventureWorksDW" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
        </Connections>
    <Packages>
        <Package Name="MySecondBimlPackage">
            <Events>
                <Event Name="ErrorHandler" EventType="OnError">
                    <Tasks>
                        <ExecuteSQL Name="Log Error" ConnectionName="AdventureWorksDW">
                            <DirectInput>EXEC usp_LogError</DirectInput>
                        </ExecuteSQL>
                    </Tasks>
                </Event>
                <Event Name="PreExecuteHandler" EventType="OnPreExecute">
                    <Tasks>
                        <ExecuteSQL Name="Log PreExecute" ConnectionName="AdventureWorksDW">
                            <DirectInput>EXEC usp_LogPreExecute</DirectInput>
                        </ExecuteSQL>
                    </Tasks>
                </Event>
            </Events>
        </Package>
    </Packages>
</Biml>

Data Flow

Perhaps the most powerful feature of SSIS is the dataflow. The Dataflow is a specific type of SSIS task that enables you to provide row-by-row processing and transformation logic for a variety of data sources and destinations. This is a great complement to set-based processing offered by relational database queries.

The dataflow task permits you to define a sequence of source, transformation, and destination components that operate on data one row at a time. You can also define dataflow paths among components that dictate how data rows will flow from one component to the next.

As with SSIS control flow tasks, there are a great variety of built-in SSIS dataflow components, many custom components implemented by 3rd party vendors, and the ability to implement your own custom components. To begin using these components, you must simply create a Dataflow task with a child <Transformations> collection element and then populate that collection element with the desired components. Here is an example of creating a simple dataflow task with a single component:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="AdventureWorksDW" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Packages>
        <Package Name="MyThirdBimlPackage">
            <Tasks>
                <Dataflow Name="DataflowTask1">
                    <Transformations>
                        <OleDbSource Name="OLEDBSource1" ConnectionName="AdventureWorksDW">
                            <DirectInput>SELECT * FROM dbo.DimAccount</DirectInput>
                        </OleDbSource>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Built-in Components

Biml has first-class support for all of the "built-in" dataflow components. By that, I mean all of the components that appear in the SSIS toolbox when you do a fresh install of SSIS and all of those add-in components that are distributed by Microsoft. For reference, here is a list of the Biml elements for each of them:

Over time, we will be adding tutorials for using each of these components, but in the meantime, the reference documentation should get you pointed in the right direction.

Custom Components

While support for the built-in components is sufficient to implement the vast majority of SSIS packages, sometimes you need to use custom components that have been implemented either by one of your developers or by a third party vendor (e.g. PragmaticWorks TaskFactory or CozyRoc SSIS+).

In those cases, you can use the Biml CustomComponent syntax. To use the CustomComponent syntax, you do need to have some understanding of how the CustomComponent chooses to store its configuration data within its inputs, outputs, columns, and properties. Normally, the most convenient way of discovering the appropriate configuration information is to import a package using the desired custom component using the Mist IDE.

For an example of using CustomComponent syntax with the CozyRoc SSIS+ Lookup Plus component, take a look at: http://bimlscript.com/Snippet/Details/64

Dataflow Paths

We've already been introduced to precedence constraints for control flows in Biml. Furthermore, we've seen how we have the options of explicitly specifying precedence constraints or allowing them to be created automatically by using ConstraintMode="Linear".

Dataflow paths are different from precedence constraints in a few important ways: - All control flow tasks can all have as many input or output precedence constraints as desired. Almost all dataflow components have a fixed number of input and output paths - that fixed number is usually "1". - When dataflow components have a fixed number of output paths, one of those output paths is the "preferred" output path that is most commonly used. For example, most components prefer their "Output" path over their "Error" path, since the Output path is most commonly used. This is in contrast to control flow precedence constraints where there is no concept of a preferred path. - Dataflow paths can be configured to customize columns, error handling, and other information. However, this is quite rare in practice - much more rare than custom configuration of a control flow precedence constraint.

As a result of the above differences, we use a different approach for specifying dataflow paths as opposed to control flow precedence constraints.

Implicit Dataflow paths

If you do not explicitly specify an input path for a component in Biml, the Biml compiler will check to see if the preceding component has a preferred output path. If it does, the Biml compiler will automatically create a dataflow path from the preferred output of the predecessor component to the input of the successor component. That may sound complicated, but in practice, it has the effect of significantly simplifying your Biml code - especially in automation scenarios that you'll learn more about later.

Let's take a look at a simple example:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="AdventureWorksDW" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Packages>
        <Package Name="MyThirdBimlPackage">
            <Tasks>
                <Dataflow Name="DataflowTask1">
                    <Transformations>
                        <OleDbSource Name="OLEDBSource1" ConnectionName="AdventureWorksDW">
                            <DirectInput>SELECT * FROM dbo.DimAccount</DirectInput>
                        </OleDbSource>
                        <OleDbDestination Name="OLEDBDestination1" ConnectionName="AdventureWorksDW">
                            <ExternalTableOutput Table="dbo.DimAccount" />
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Note that I did not specify an input path for OLEDBDestination1. But OLEDBSource1 has a preferred output path (i.e. it's standard output as opposed to its error output). Consequently, the Biml compiler will automatically connect these two components.

For implicit dataflow paths, order matters. If you put the OleDbDestination before the source, there will be no automatic path creation, because OleDbSource does not support input paths and OleDbDestination has no preferred output paths.

Explicit Dataflow paths

But what if you need to attach to a non-preferred output path, or you don't want to enforce strict ordering of your components in the Biml code, or maybe you just prefer to be explicit about your dataflow path definitions. Alternatively, maybe you need to provide additional configuration options on your dataflow paths such as column mappings. Biml provides a convenient syntax to do all of this.

Let's start with just a simple explicit dataflow path definition:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="AdventureWorksDW" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Packages>
        <Package Name="MyThirdBimlPackage">
            <Tasks>
                <Dataflow Name="DataflowTask1">
                    <Transformations>
                        <OleDbSource Name="OLEDBSource1" ConnectionName="AdventureWorksDW">
                            <DirectInput>SELECT * FROM dbo.DimAccount</DirectInput>
                        </OleDbSource>
                        <OleDbDestination Name="OLEDBDestination1" ConnectionName="AdventureWorksDW">
                            <ExternalTableOutput Table="dbo.DimAccount" />
                            <InputPath OutputPathName="OLEDBSource1.Output" />
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

You'll notice that the only difference from the implicit path case is the addition of an <InputPath> element. Input paths and output path definitions provide a variety of other options for column mappings and similar configuration. To keep this lesson from becoming too long, we'll leave those for you to experiment with on your own.

Connections

You've already seen several examples of specifying connections in a Biml file. This is done by adding connection elements of the desired type under the root Biml element. This is sufficient if you intend to use these connections from your SSIS packages without any additional package-specific configuration. But what if you want to override properties of the connection with SSIS expressions? To do this, you simply need to reference the connections from your package and supply the additional configuration information. Here is an example of how to do this:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="AdventureWorksDW" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Packages>
        <Package Name="MyFourthBimlPackage">
            <Connections>
                <Connection ConnectionName="AdventureWorksDW">
                    <Expressions>
                        <Expression ExternalProperty="ConnectionString">"Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;"</Expression>
                    </Expressions>
                </Connection>
            </Connections>
        </Package>
    </Packages>
</Biml>

Note that you do not need to add connection references to your package if you do not intend to customize their configuration for that package. The Biml compiler will automatically create default connection references for all of those connections that you reference from within your package.

Script Projects

As we discussed earlier, SSIS is highly extensible via custom tasks and dataflow components. However this extensibility mechanism comes at a high cost. It requires complex .NET code development and requires that you install the resulting custom tasks/components on the SSIS server. Both of these limitations are prohibitive for some organizations. As a result, SSIS offers another option for extensibility: Script Tasks and Script Components.

Script tasks and components enable you to write a small amount of .NET code that can take the place of a custom task/component. Script tasks do not need to be installed on the server, and they offer a simplified developer interface that makes them easier to implement and maintain than custom tasks/components. Perhaps the only major downside of script tasks/components is that they are not easily reusable. If you wish to use the same script task in multiple locations, your only option in BIDS/SSDT is to copy and paste. If you later need to modify these script tasks/components, you must separately modify every instance.

Biml provides a much better model. The <ScriptProjects> collection element is a child of the root Biml element. This means that you can centrally define a script project and then reference it from as many packages as you like. If you need to make changes, you can do so just once and then recompile.

Let's take a look at a minimal sample:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <ScriptProjects>
        <ScriptTaskProject Name="TaskScriptProject1" ProjectCoreName="ST_39671a219bda455a9ac64f4384d1090b">
            <AssemblyReferences>
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS.dll" />
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask.dll" />
                <AssemblyReference AssemblyPath="System.dll" />
            </AssemblyReferences>
            <Files>
                <File Path="ScriptMain.cs">
                    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
                    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
                    {
                        public void Main()
                        {
                            Dts.TaskResult = (int)Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;
                        }
                    }
                </File>
            </Files>
        </ScriptTaskProject>
    </ScriptProjects>
    <Packages>
        <Package Name="MyFourthBimlPackage" ProtectionLevel="EncryptSensitiveWithUserKey">
            <Tasks>
                <Script Name="ScriptTask1">
                    <ScriptTaskProjectReference ScriptTaskProjectName="TaskScriptProject1" />
                </Script>
            </Tasks>
        </Package>
    </Packages>
</Biml>

You'll notice that TaskScriptProject1 is defined under the <ScriptProjects> element. This script project is then separately referenced from within MyFourthBimlPackage. The best thing about this approach is that we can now add several more packages and script tasks - each of which references the same centrally defined TaskScriptProject1. Consequently, we now have a script project solution with unprecedented code reuse and versioning/maintenance capabilities. No longer do you need to update every instance of a script task or component when you need to make changes. Just modify the centrally defined script project and rebuild!

File Formats

A powerful feature of SSIS dataflow tasks is the ability to process flat text files and raw binary files. Much like script projects, Biml enables you to define your flat and raw file formats in a central location and then reuse those formats across multiple file connection managers, sources, and destinations.

The definition of file formats in Biml is very similar to the way we define relational tables in Biml. As a result, I won't spend too much time covering the details of the syntax here. Here is a simple example of defining and using a flat file format using Biml:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <FileFormats>
        <FlatFileFormat Name="FlatFileFormat1" FlatFileType="Delimited" RowDelimiter="CRLF">
            <Columns>
                <Column Name="CustomerID" Delimiter="Comma" />
                <Column Name="FirstName" DataType="String" Length="256" Delimiter="Comma" />
                <Column Name="LastName" DataType="String" Length="256" Delimiter="CRLF" />
            </Columns>
        </FlatFileFormat>
    </FileFormats>
    <Connections>
        <FlatFileConnection Name="FlatFileConnection1" FileFormat="FlatFileFormat1" FilePath="C:\FlatFiles\Customer.csv" />
    </Connections>
    <Packages>
        <Package Name="MyFifthBimlPackage">
            <Tasks>
                <Dataflow Name="Dataflow1">
                    <Transformations>
                        <FlatFileSource Name="FlatFileSource1" ConnectionName="FlatFileConnection1" />
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

To recap, the steps to use a flat or raw file with Biml are:

  1. Create the FlatFileFormat or RawFileFormat centrally within a root <FileFormats> collection element.
  2. Create one or more FlatFileConnections or RawFileConnections that reference the file format.
  3. Create one or more FlatFileSource/RawFileSource or FlatFileDestination/RawFileDestination components that references each of the file connections.

Project Deployment Model

In SQL Server 2012, Microsoft added a major new feature to SSIS called project deployment model. Under package deployment model, each package would be managed and run independently. With the new project deployment model, packages were able to share resources such as connections and project parameters across many packages. Furthermore, the project could be bundled into a single deployment ISPac file that could be easily managed centrally on the SSIS server.

Biml fully supports both SSIS package and project deployment models. When compiling from within BIDSHelper, the Biml compiler will automatically detect the deployment model that is being used by the SSIS project. When compiling from Mist, you can select which deployment model you would like to use in project settings.

If you are using project deployment model several new Biml options become available.

Project Connections

When you use project deployment model, the Biml compiler will automatically create a package project with all of the necessary settings. By default, all of your connections will remain as package connections - just as they are in BIDS/SSDT. In order to specify that a connection should be created at the project level, all you need to do is to add CreateInProject="true" to your connection definition. Here is an example:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="AdventureWorksDW" CreateInProject="true" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
</Biml>

Package Projects

Using the CreateInProject attribute on connections is sufficient for most people who are first switching from package to project deployment model. However, once you decide to take advantage of more of the features available in the project deployment model, you must write additional Biml code to define your package project options. Here is an example of a PackageProject with common options set:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="AdventureWorks" ConnectionString="Server=localhost;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
        <OleDbConnection Name="AdventureWorksDW" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Packages>
        <Package Name="MyFirstBimlPackage" />
        <Package Name="MySecondBimlPackage" />
        <Package Name="MyThirdBimlPackage" />
    </Packages>
    <Projects>
        <PackageProject Name="Project1">
            <Connections>
                <Connection ConnectionName="AdventureWorksDW" />
            </Connections>
            <Packages>
                <Package PackageName="MyFirstBimlPackage" IsStartupObject="true" IsEntryPoint="true" />
                <Package PackageName="MySecondBimlPackage" />
            </Packages>
            <Parameters>
                <Parameter Name="Param1" DataType="Int32">0</Parameter>
            </Parameters>
        </PackageProject>
    </Projects>
</Biml>

There are a few important things to note:

  1. Package projects allow you to specify exactly which packages are included at the project level. Notice that MyThirdBimlPackage is not included in the project. That package will still be built, but it will not be included in the project. This is particularly useful when using Mist to create and manage multiple projects with packages shared among them.
  2. Package projects also allow you to specify exactly which connections are included at the project level. In this case, AdventureWorksDW is included, but AdventureWorks is not. This means that while AdventureWorksDW will be configured as a project-level connection, AdventureWorks will be configured as a package-level connection in all of the packages it is referenced from.
  3. Parameters can be added to your project using fairly simple syntax.

Package Projects in BIDSHelper

You may find that some of the project configuration features do not work comprehensively within BIDSHelper. Unlike Mist, Biml files in BIDSHelper are being built within the context of an existing SSIS package project, which places some limitations on our ability to modify the project. In the worst case, this will require that you use the SSDT user interface to add project parameters and some other options to your BIDSHelper Biml project.

Package Configurations

SSIS offers a feature call package configurations that makes it easy to create external files that override specific package values during execution. Package configurations can reside either on a SQL Server or within files on the SSIS server. Note that package configurations have been replaced by environments and other features that become available once you switch to the SSIS project deployment model - so this section only practically applies if your are using an older version of SSIS or prefer to use the package deployment model with the more recent versions of SSIS.

Automatic Package Configurations

It is fairly common for package configurations to be used for the connection string of your SSIS connection managers. In fact, this is so common, that Biml offers a setting to create these package configurations automatically for you. To automatically create an XML file package configuration for your package, simply set the AutoCreateConfigurationsType attribute to Xml as follows:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="AdventureWorksDW" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Packages>
        <Package Name="MyFourthBimlPackage" AutoCreateConfigurationsType="Xml">
            <Tasks>
                <ExecuteSQL Name="Run Script" ConnectionName="AdventureWorksDW">
                    <DirectInput>SELECT 1 AS Foo</DirectInput>
                </ExecuteSQL>
            </Tasks>
        </Package>
    </Packages>
</Biml>

If you would prefer to store your package configuration in your database, you can set AutoCreateConfigurationsType to Sql and then also provide a reference to the table that will be used to store the configuration using the AutoCreateConfigurationsTableName property as follows:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="AdventureWorksDW" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="AdventureWorksDW" />
    </Databases>
    <Tables>
        <Table Name="SsisConfigurations" SchemaName="AdventureWorksDW2012.[default]">
            <Columns>
                <Column Name="ConfigurationFilter" DataType="String" Length="255" />
                <Column Name="ConfiguredValue" DataType="String" Length="255" IsNullable="true" />
                <Column Name="PackagePath" DataType="String" Length="255" />
                <Column Name="ConfiguredValueType" DataType="String" Length="20" />
            </Columns>
        </Table>
    </Tables>
    <Packages>
        <Package Name="MyFourthBimlPackage" AutoCreateConfigurationsType="Sql" AutoCreateConfigurationsTableName="AdventureWorksDW2012.[default].SsisConfigurations">
            <Tasks>
                <ExecuteSQL Name="Run Script" ConnectionName="AdventureWorksDW">
                    <DirectInput>SELECT 1 AS Foo</DirectInput>
                </ExecuteSQL>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Manual Package Configurations

While the automatic creation of package configurations can be very convenient, you often desire more fine grained control over the values contained in your package configuration and its placement on disk. In those cases, you can manually configure your package configurations by adding a <PackageConfigurations> collection element to your package and adding all of the desired configuration information. Here is a brief example:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="AdventureWorksDW" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Packages>
        <Package Name="MyFourthBimlPackage">
            <Tasks>
                <ExecuteSQL Name="Run Script" ConnectionName="AdventureWorksDW">
                    <DirectInput>SELECT 1 AS Foo</DirectInput>
                </ExecuteSQL>
            </Tasks>
            <PackageConfigurations>
                <PackageConfiguration Name="Configuration1">
                    <ExternalFileInput ExternalFilePath="C:\PackageConfigurations\Configuration1.dtsConfig" />
                    <ConfigurationValues>
                        <ConfigurationValue Name="ConnectionString" PropertyPath="AdventureWorksDW.ConnectionString" DataType="String" Value="InitialValuePlaceholder" />
                    </ConfigurationValues>
                </PackageConfiguration>
            </PackageConfigurations>
        </Package>
    </Packages>
</Biml>

As package configurations are finding decreased usage with the introduction of project deployment model, I will leave it to you to review the reference documentation for package configurations if you'd like to find out more about manually creating package configurations in database tables, environment variables, registry entries, or parent package variables: https://varigence.com/Documentation/Language/Element/AstPackageConfigurationNode

Logging

SSIS provides a built-in logging mechanism that will automatically write preselected values to your chosen log provider when specified events occur on a given task. Especially after project deployment mode and the SSIS project catalog became available, this is no longer a commonly used SSIS feature, so I will provide just a simple working example:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FileConnection Name="XmlFile" FilePath="C:\Logging\XmlLog.xml" />
        <FileConnection Name="TextFile" FilePath="C:\Logging\TextLog.txt" />
    </Connections>
    <Packages>
        <Package Name="MyFourthBimlPackage">
            <LogProviders>
                <XmlLogProvider Name="XmlLogProvider" ConnectionName="XmlFile" />
                <TextLogProvider Name="TextLogProvider" ConnectionName="TextFile" />
            </LogProviders>
            <LogEvents>
                <LogEvent EventName="OnError">
                    <EventColumns>
                        <EventColumn>Computer</EventColumn>
                        <EventColumn>SourceID</EventColumn>
                        <EventColumn>SourceName</EventColumn>
                    </EventColumns>
                </LogEvent>
            </LogEvents>
        </Package>
    </Packages>
</Biml>

For more information about configuring log events and log providers, consult the following Biml reference documentation articles:

Conclusion

This has been a high-level overview of the features available in Biml to create and manage SSIS packages. As you can see, Biml provides full fidelity with SSIS - if you can do it with SSIS, you can do it with Biml. You'll find in future lessons that these features become considerably more powerful as they extended with BimlScript automation.

Finished?

Complete the lesson Biml Basics for SSIS:

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

Comments

gravatar

KiranA

6:37am 01.07.16

If i have BIML file in my file local drive , how i need to execute outside of BIDS(instead of right click on BIML in BIDS to automate from Application) to generate SSIS packages to local drive

gravatar

Patrick77

5:50pm 02.04.16

When I use Biml to create a connection manager (OleDB in this case) it does not actually specify the server name? This creates errors in ALL connection managers and makes me MANUALLY need to edit the connection manager to type in the ServerName from the dropdown?!?!?

gravatar

SQLAddict

9:10am 01.17.18

Computer SourceID SourceName

This part doesn't work. When the package is generated the log provider is disabled and hence manual intervention is necessary in order to enabled it and have the appropriate events logged. Can you please let us know the magic formula to make it automatically enabled?

gravatar

Satishakumar

5:54am 07.30.18

The type or namespace name 'Dts' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?),

https://stackoverflow.com/questions/51538757/the-type-or-namespace-name-dts-does-not-exist-in-the-namespace-microsoft-sqls, I am facing this issue with BIML Express and generated script task inside SSIS package having issue while opening package file is vs 2015

gravatar

Toufeeq

12:48pm 03.07.19

<OleDbConnection Name="Connection1" ConnectionString="Server=localhost The above doe snot seem to add the server name into the connection property

The below seems to work perfectly for me <OleDbConnection Name="AdventureWorksDW" ConnectionString="Data Source=localhost

gravatar

Dheeraj8

4:40am 07.15.19

In the content, at constraint conditions paragraph some of the lines are missing. It ended abruptly, hope you will fix it.