[this intelligent life]
Blog Post

Using .Net Objects in BIML

Mark Wojciechowicz • Jun 09, 2017

One handy thing with BIML, the T4 Templating tool for SSIS, is that you can reference cs files which can contain a .net class with helper methods. This can help keep your biml code clean and readable.

In this example, I'll show how to iterate over a list to generate custom dataflow tasks. Some BIML examples illustrate generating dataflows based on a set of tables. However, it does not seem very realistic that the source query and target table look precisely the same. This example will assume we have some custom query and a target table.

Previously, I have used a similar method which pulled metadata from a database table. However, I find that it is difficult to maintain a project across contributors because everyone needs to insure that the table data is refreshed before regenerating the biml. Also, it's difficult to troubleshoot because the data driving the solution is in a separate place from the code. This example puts the code and metadata in one place.

First, we will add a cs file to the project with this silly method:

  1. Write click the project > Add Biml File
  2. Rename the Biml file to PackageSource.cs
  3. Replace the biml template with the following code:

 using 
 System.Collections.Generic 
; public 
 class 
 PackageSource 
{ public 
 string 
TargetTable { get 
; set 
; } public 
 string 
SourceQuery { get 
; set 
; } private 
 PackageSource 
( string 
targetTable, string 
sourceQuery)
    { this.TargetTable = targetTable; this.SourceQuery = sourceQuery;
    } public 
 static 
List<PackageSource> GetSources()
    {
        List<PackageSource> packageSources = new 
List<PackageSource>();
        packageSources.Add( new 
PackageSource( "Subscriptions" 
, @"SELECT [SubscriptionID] 
 ,[OwnerID] 
 ,[Report_OID] 
 ,[Locale] 
 ,[InactiveFlags] 
 ,[ExtensionSettings] 
 ,[ModifiedByID] 
 ,[ModifiedDate] 
 ,[Description] 
 ,[LastStatus] 
 FROM [dbo].[Subscriptions]" 
));

        packageSources.Add( new 
PackageSource( "ActiveSubscriptions" 
, @"SELECT [ActiveID] 
 ,[SubscriptionID] 
 ,[TotalSuccesses] 
 ,[TotalFailures] 
 FROM [dbo].[ActiveSubscriptions]" 
)); return 
packageSources;
    }

}

In the PackageSource class we have the properties TargetTable and SourceQuery. The constructor will set these values with a new instance of the class. The method GetSources will return a list of package sources. It creates a list and adds PackageSource objects to them. Any time that we want to add a new source, we simple insert another packageSources.Add() line.

Next, we add another biml file to the project and use the following code:

< #@ 
code file= "PackageSource.cs" 
 # 
>
<Biml xmlns= "http://schemas.varigence.com/biml.xsd" 
>
    <Connections>
        <Connection Name= "ReportServerSource" 
ConnectionString= "data source=.;initial catalog=ReportServer;Provider=SQLNCLI11.1;Integrated Security=sspi;" 
/>
        <Connection Name= "ReportServerTarget" 
ConnectionString= "data source=.;initial catalog=ReportServer;Provider=SQLNCLI11.1;Integrated Security=sspi;" 
/>
    </Connections>
    <Packages>
        <Package Name= "ReportServerExtract" 
>
            <Connections>
                <Connection ConnectionName= "ReportServerSource" 
/>
                <Connection ConnectionName= "ReportServerTarget" 
/>
            </Connections>
            <Tasks>
                < # 
 var 
sources = PackageSource.GetSources(); foreach 
( var 
source in 
sources)
                    { # 
>
                <Dataflow Name= "<#=source.TargetTable#>" 
>
                    <Transformations>
                        <OleDbSource Name= "Read From <#=source.TargetTable#>" 
ConnectionName= "ReportServerSource" 
>
                            <DirectInput>< # 
=source.SourceQuery # 
></DirectInput>
                        </OleDbSource>
                        <OleDbDestination Name= "Insert to <#=source.TargetTable#>" 
ConnectionName= "ReportServerTarget" 
>
                            <ExternalTableOutput Table= "<#=source.TargetTable#>" 
/>
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
                < # 
} # 
>
            </Tasks>
        </Package>
    </Packages>
</Biml>

At the top of the biml file, we add a code file reference. This must be before the biml tag or it won't compile. In this example, I used the reportserver database, figuring that most SQL dev's would have a local copy and be able to compile this code.

In the tasks section, we declare a variable and call the PackageSource.GetSources() method. Then we loop through each package source instance, plugging in the values of a package source - source.TargetTable and source.SourceQuery - into the oledb source and destination components. BIML handles the magic of mapping columns. Once generated, we get a package like this:

Enjoy!

Share by: