World of Whatever

A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Loading...

Wednesday, August 10, 2016

Biml Hero Training, Day 1

In June of 2013, I created my first SSIS package with Biml. Three years later, I have come so far except that today was my first day of Biml Hero training. Holy cow there's a lot I have yet to learn. While I can't go into the details of the training due to the non-disclosure agreement, I wanted to take a moment and share some of the public things.

StructureEqual

The base object for all things biml, AstNode, StructureEqual method. If I understood it correctly, I could use this method to determine whether my biml representation of an object, like a table, is the same as a table that I just reverse engineered. That's pretty cool and something I'll need to play with. And remembering harder, Scott once said something about how you could use Biml as a poor man's substitute for Schema Compare. I bet this is the trick to that.

designerbimlpath

As Cathrine notes, setting this attribute will give intellisense a shove in the right direction for fragments.

Extension methods

Technically, I already picked this trick up at Cathrine's excellent session a

Topological sorting

This was an in-depth Extension method but as with any good recursive algorithm it was precious few lines of code. Why I care about it is twofold: execution dependencies and as I type that, I realize lineage tracing would also fall under this, and foreign key traversal. For the former, in my world, I find I have the best success when my SSIS packages are tightly focused on a task and I use a master/parent package to handle the coordination and scheduling of sub-package execution. One could use an extension method to discover all the packages that implement an Execute Package Task and then figure out the ordering of dependent tasks. That could save me some documentation headaches.

Foreign key traversal is something that I think would be rather clever to do in Biml. When I reverse engineer a database, I can already pull in foreign key columns. What I can't do, at least easily with the current version is to figure out what the referenced table/column is. Think about it, if I know column SellerId in FactSales is foreign keyed to column Id in DimSeller (this is exposed in sys.foreign_key_columns) and SellerName is defined as unique, I could automate the building of lookups (based on name matches). If my fact's source query looks like SELECT SaleDate, Amount, SellerName FROM stagingTable, I could see if column names matched and auto inject lookups into my fact load.

Those were my public highlights. Tomorrow's another day and I can't wait to see what we do.

Wednesday, August 3, 2016

Biml Reverse Engineer a database

Biml Reverse Engineer a database, a.k.a. Biml to the rescue

I'm at a new client and I needed an offline version of their operation data store, ODS, database schema. I don't know what I was expecting, but it wasn't 11,500 tables. :O That's a lot. First up to bat was Visual Studio Database Projects. I clicked Import and you really have no options to winnow the list of items your importing down via Import. Ten minutes later, the import timed out on spatial indexes. Which wouldn't be so bad except it's an all or nothing operation with import.

Fair enough, I'll use the Schema Comparison and only compare tables, that should make it less ugly. And I suppose it did but still, the operation timed out. Now what?

SSMS to the rescue. I right click on my database and select Generate Scripts and first off, I script everything but the tables. Which is amusing when you have 11.5k tables, checking and unchecking the table box causes it to spin for a bit. I generated a file for each object with the hope that if the operation goes belly up, I'll at least have some progress. Round 1, Winner! I had all my views, procedures, functions, data types (don't hate), all scripted out nice and neat. Round 2, I just selected tables. And failed.

Maybe I didn't want all the tables. They have the ODS broken out by schemas to identify the data source and I only wanted the CMS data for this first draft. I run back through the Generate Scripts wizard this time only selecting tables in the CMS schema. That significantly reduced the number of objects I needed to script but still, it failed. And my mouse finger was tired. There had to be a better way.

Of late, Biml seems to be that better way. In just a few lines, I created a connection to my database, reverse engineered the targeted schema and then wrote the SQL out to files (so I could then import them with a database project). How cool is that?

inc_Connections.biml

I first added a biml file to my SSIS project that contained an OLE DB Connection Manager to the database I was interested in.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="ODS" ConnectionString="Data Source=localhost\DEV2014;Initial Catalog=ODS;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
    </Connections>
</Biml>

ExportTables.biml

Here's the "magic". There are three neat tricks in here.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#@ template tier="1" #>
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<#@ import namespace="System.IO" #>
<#
    var schema = new List<string>{"CMS"};
    var ODSCM = RootNode.OleDbConnections["ODS"];
    var ODSDB = ODSCM.GetDatabaseSchema(schema, null, ImportOptions.None);
    string fileNameTemplate = @"C:\Users\fellowsb\Documents\ODSDB\{0}_{1}.sql";
    string currentFileName = string.Empty;
    foreach (var table in ODSDB.TableNodes) 
    { 
        currentFileName = string.Format(fileNameTemplate, table.Schema.Name, table.Name);
        System.IO.File.WriteAllText(currentFileName, table.GetDropAndCreateDdl());
    } 
#>   
</Biml>

Tiering

The first neat thing is line 2. I have a directive that tells the biml compiler that this is a tier 1 file. I could have specified tier 3, tier 7, or tier 10, it really doesn't matter as long as this is greater than the value in inc_Connections.biml. Since I didn't specify a tier in that file, it's tier 0. I needed to use an explicit tier here because line 7 references an object in the RootNode (my connection manager) that won't be built until the connections file has been compiled. The take away for tiering: if you're objects in the Biml object tree, you might need to specify tiers to handle build dependencies.

GetDatabaseSchema

Cathrine Wilhelmsen (b|t) did an excellent job covering GetDatabaseSchema so I'll let you read her post and simply comment that this method allowed me to just reverse engineer the schema I was interested.

GetDropAndCreateDdl

The last bit of magic is GetDropAndCreateDdl. It's an extension method that allows me to take the in memory representation of the table and emit the TSQL required to create that object. I enumerate through my TableNodes collection and for each object, I call the GetDropAndCreateDdl method and dump that to a file.

Gist available

Wednesday, July 20, 2016

Biml Transformer Update a variable value

Biml

In preparation for Biml Hero training, I thought it would be a good idea to understand Biml Transformers. I have read that article many times but never really dug in to try and understand it, much less find a situation where it'd be something I needed. This post covers my first attempt to using one.

Use case: updating a variable value

Assume you have an SSIS variable in your Biml files that you need to update the value - the server died and you need a new server name patched in. You could do a search and replace in your biml, or apply a configuration once you emit and deploy the SSIS package but let's try the transformer.

Source biml

Let's use the following simple biml as our package. It's three sequence containers in a serial connection and a single variable ServerName with a value of SQLDEV.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="OriginalPackage_LocalMerge" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="String" Name="ServerName">SQLDEV</Variable>
            </Variables>
            <Tasks>
                <Container Name="SEQ 1" />
                <Container Name="SEQ 2" />
                <Container Name="SEQ 3" />
            </Tasks>
        </Package>
    </Packages>
</Biml>

Transformer biml

Our transformer is simple. We've specified "LocalMerge" as we only want to fix the one thing. That one thing, is an SSIS Variable named "ServerName".

What is going to happen is that we will redeclare the variable, this time we will specify a value of "SQLQA" for our Value. Additionally, I'm going to add a Description to my Variable and preserve the original value. The TargetNode object has a lot of power to it as we'll see over this series of posts on Biml Transformers.

<#@ target type="Variable" mergemode="LocalMerge" #>
<#
    string variableName = "ServerName";
    if (TargetNode.Name == variableName)
    {
#>
    <Variable DataType="String" Name="ServerName">SQLQA<Annotations><Annotation AnnotationType="Description">Value was <#=  TargetNode.Value#></Annotation></Annotations></Variable>
<#
    }
#>

So what's happening?

I think it's important to understand how this stuff works, otherwise you might not get the results you expect. The Biml compiler is going to take our source biml. If I had code nuggets in there, those get expanded and once all the "normal" biml is complete, then our transformers swoop in and are executed. This allows the transformers to work with the finalized objects, metadata is set and all that, prior to rendering actual SSIS packages.

Result

It doesn't look that amazing, I admit.

<Variable DataType="String" Name="ServerName">SQLQA
    <Annotations>
        <Annotation AnnotationType="Description">Value was SQLUAT</Annotation>
    </Annotations>
</Variable>
But conceptually, wouldn't it be handy to be able to selectively modify bits of a package? Someone didn't name their Tasks or Components well? You could have a transformer fix that. Someone forgot to add your logging/auditing/framework code? You could have a transformer fix that too!

Start thinking in Biml! Most of my answers on StackOverflow have biml in them simply because it makes describing ETL so much easier.

Caveats

Transformers require a license for Mist. They don't work in BIDS Helper, BimlExpress or BimlOnlien.

Friday, July 15, 2016

Building SSIS packages using the Biml object model

Programmatically building SSIS packages via the Biml Object Model

I thought it might be fun to try and figure out how to use the Biml Api to construct SSIS packages. This post is the first in the occasional series as I explore and find neat new things.

Getting Started

The most important precursor to doing this is you will need a licensed installation of Mist. Full stop. The assemblies we're going to use have security built into them to tell whether they are licensed and you cannot use the assemblies shipped with BidsHelper or BimlExpress as they've hardwired to the specific apps.

We're going to use two classes: AstRootNode and AstPackageNode.

Ast, what is that? Abstract Syntax Tree - it's a compiler theory thing.

AstRootNode? The root node is the <Biml /> tag. It contains all the collections in your biml declaration.

AstPackageNode? This is an instance of an SSIS package.

using Varigence.Languages.Biml;
using Varigence.Languages.Biml.Task;
using Varigence.Languages.Biml.Transformation;

...

            AstRootNode arn = new AstRootNode(null);
            AstPackageNode pkg = new AstPackageNode(arn);
            arn.Packages.Add(pkg);

            pkg.Name = "HelloWorld";

Now what?
You have two choices, you can get the Biml

Console.WriteLine(arn.GetBiml());

which results in

<Biml >
    <Packages>
        <Package Name="HelloWorld" />
    </Packages>
</Biml>

Or you can get the Xml

Console.WriteLine(arn.EmitAllXml());

which looks like

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

In this example, they're nearly identical except the Xml emission results in the namespace existing in the Biml declaration while the GetBiml call just returns Biml. Interestingly enough, if either is fed through BimlExpress, they'll both pass validation.

Wednesday, May 25, 2016

SSIS - What is the name of the file

What is the name of the current file

It's common to not know the exact name of the file you're loading. You often have to apply some logic - a file mask, date calculation logic, etc to determine what file needs to be loaded. In SSIS, we often use a ForEach File Enumerator to accomplish this but an Expression task or even an Execute SQL Task can be used to retrieve/set a file name. Today, I'm going to show you two different mechanisms for identifying the current file name.

Did you know that in SSIS, the Flat File Source exposes an Advanced property called FileNameColumnName. This shows in your Properties window for the Flat File Connection Manager

There are two different click paths for setting the FileNameColumnName property. The first is to right click on the Flat File Source and select the "Show Advanced Editor" option. There, navigate to Component Properties and you can set the FileNameColumnName property there.


The second is a combination of the Properties window and the Flat File Source itself. Select the Flat File Source and go to the Properties window. There you specify the FileNameColumnName property but notice, the Flat File Source itself is put into a Warning state. To fix that, we need to double click on the component and view the Columns tab. You'll notice the name we specified in the Properties window is now set in the Columns tab and the warning goes away.


Cool story, bro

That's cool and all, but it has two downsides. The first is due to me being a dumb American but the file name that is added to the data flow is DT_WSTR/unicode/nvarchar with a length of 260. That is awesome for internationalization to default to it. Except the systems I work in never have nvarchar defined so now I will need to use a Data Conversion task to change the supplied name into a non-unicode version. That's an irritant but since I know what the pattern is, I can live with it.

The real downside with this approach is that it only works for Flat File Source. Excel, Raw File Source, and XML sources do not expose the FileNameColumnName property. Now that is a problem in my book because when I'm automating, I'd have to have one set of source patterns for flat files and a different one for non-flat files.

A better approach

So, as much as I like the built in solution, my pattern is to use a Derived Column to inject the file name into the Data Flow. I have a variable called CurrentFileName in all my packages. That contains the design-time path for my Flat File Connection Manager (or Excel). My Connection Manager will then have the ConnectionString/ExcelFilePath property assigned to be @[User::CurrentFileName]. This positions me for success because all I need to do is ensure that whatever mechanism I am using to determine my source file correctly populates that variable. In this post, a ForEach File Enumerator will handle that.

Within my Data Flow Task, I will add a Derived Column Transformation that adds my package variable into the data flow as a new column. Here, I am specifying it will be of data type DT_STR with a length of 130.

Biml

What would a post be without some Biml to illustrate the point?
I use the following as my source file.

Col1|Col2
1|2
2|3
3|4

This Biml will generate an SSIS package that has a Data Flow with a Flat File Source, a Derived Column and a Row Count. What you're interested in is seeing how we either specify the value for the FileNameColumnName in our FlatFileSource tag or enrich our data flow by adding it in our Derived Column component.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FlatFileConnection FilePath="C:\ssisdata\SO\Input\CurrentFileName_0.txt" FileFormat="FFF_CurrentFileName" Name="FFCM" />
    </Connections>
    <FileFormats>
        <FlatFileFormat Name="FFF_CurrentFileName" IsUnicode="false" FlatFileType="Delimited" ColumnNamesInFirstDataRow="true">
            <Columns>
                <Column Name="Col1" DataType="Int32" Delimiter="|" />
                <Column Name="Col2" DataType="Int32" Delimiter="CRLF" />
            </Columns>
        </FlatFileFormat>
    </FileFormats>
    <Packages>
        <Package Name="CurrentFileName">
            <Connections>
                <Connection ConnectionName="FFCM">
                    <Expressions>
                        <Expression ExternalProperty="ConnectionString">@[User::CurrentFileName]</Expression>
                    </Expressions>
                </Connection>
            </Connections>
            <Variables>
                <Variable DataType="String" Name="CurrentFileName">C:\ssisdata\SO\Input\CurrentFileName_0.txt</Variable>
                <Variable DataType="Int32" Name="RowCountSource">0</Variable>
            </Variables>
            <Tasks>
                <ForEachFileLoop 
                    Folder="C:\ssisdata\SO\Input" 
                    FileSpecification="CurrentFileName*.txt" 
                    Name="FELC Shred txt">
                    <VariableMappings>
                        <VariableMapping VariableName="User.CurrentFileName" Name="0" />
                    </VariableMappings>
                    <Tasks>
                        <Dataflow Name="DFT Import data">
                            <Transformations>
                                <FlatFileSource ConnectionName="FFCM" Name="FFCM Pull data" FileNameColumnName="CurrentFileNameSource" />
                                <DerivedColumns Name="DER Add CurrentFileName">
                                    <Columns>
                                        <Column DataType="AnsiString" Name="CurrentFileName" Length="130">@[User::CurrentFileName]</Column>
                                    </Columns>
                                </DerivedColumns>
                                <RowCount VariableName="User.RowCountSource" Name="CNT Source Data" />
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                </ForEachFileLoop>
            </Tasks>
        </Package>
    </Packages>
</Biml>

If all goes well, when you run you package you should see something like the following

Friday, April 22, 2016

Biml SSIS Foreach Nodelist Container (aka Shred XML in SSIS)

Biml SSIS Foreach Nodelist Container (aka Shred XML in SSIS)

Every time I look at my ForEach NodeList Enumerator post, I struggle to remember how to do half of it. Plus, as I discovered today, I am inconsistent between my images. The Control Flow shows an XML structure of

<Files>
    <File>Foo.txt</File>
    <File>Bar.txt</File>
    <File>Blee.txt</File>
</Files>
but the text inside the Foreach loop would be for parsing an XML tree in this format
<Files>
    <FileName>Foo.txt</FileName>
    <FileName>Bar.txt</FileName>
    <FileName>Blee.txt</FileName>
</Files>

The resulting package looks like this - a Foreach Enumerator that shreds the XML. We assign the shredded value into our variable CurrentNode. We pass that as an argument into a script task that does nothing but print the value as an Information event.

Configuration remains comparable to the previous post with the exception of using Variables.


Running the package generates Output like the following

Information: 0x0 at SCR Echo Back, SCR Echo Back: User::CurrentNode : Foo.txt
Information: 0x0 at SCR Echo Back, SCR Echo Back: User::CurrentNode : Bar.txt
Information: 0x0 at SCR Echo Back, SCR Echo Back: User::CurrentNode : Blee.txt

Biml

The following Biml will create a Foreach nodelist enumerator that shreds an XML recordset. We pass the current node variable into our script task that echoes the value back

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Task_ForEachNodeListLoop">
            <Variables>
                <Variable DataType="String" Name="CurrentNode"></Variable>
                <Variable DataType="String" Name="SourceXML"><![CDATA[<Files><File>Foo.txt</File><File>Bar.txt</File><File>Blee.txt</File></Files>]]></Variable>
                <Variable DataType="String" Name="OuterXPath"><![CDATA[/Files/File]]></Variable>
                <Variable DataType="String" Name="InnerXPath"><![CDATA[.]]></Variable>
            </Variables>
            <Tasks>
                <ForEachNodeListLoop 
                    Name="FENLL Shred XML"
                    EnumerationType="ElementCollection"
                    InnerElementType="NodeText"
                    >
                    <VariableInput VariableName="User.SourceXML" />
                    <VariableOuterXPath VariableName="User.OuterXPath" />
                    <VariableInnerXPath VariableName="User.InnerXPath" />
                    <VariableMappings>
                        <VariableMapping VariableName="User.CurrentNode" Name="0" />
                    </VariableMappings>
                    <Tasks>
                        <Script ProjectCoreName="ST_EchoBack" Name="SCR Echo Back">
                            <ScriptTaskProjectReference ScriptTaskProjectName="ST_EchoBack" />
                        </Script>            
                    </Tasks>
                </ForEachNodeListLoop>
            </Tasks>
        </Package>
    </Packages>
    <ScriptProjects>
        <ScriptTaskProject ProjectCoreName="ST_EchoBack" Name="ST_EchoBack" VstaMajorVersion="0">
            <ReadOnlyVariables>
                <Variable Namespace="User" VariableName="CurrentNode" DataType="String" />
            </ReadOnlyVariables>
            <Files>
                <File Path="ScriptMain.cs" BuildAction="Compile">using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_EchoBack
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            bool fireAgain = false;
            string message = "{0}::{1} : {2}";
            foreach (var item in Dts.Variables)
            {
                Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), string.Empty, 0, ref fireAgain);
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}                </File>
                <File Path="Properties\AssemblyInfo.cs" BuildAction="Compile">
using System.Reflection;
using System.Runtime.CompilerServices;

[assembly: AssemblyVersion("1.0.*")]
                </File>
            </Files>
            <AssemblyReferences>
                <AssemblyReference AssemblyPath="System" />
                <AssemblyReference AssemblyPath="System.Data" />
                <AssemblyReference AssemblyPath="System.Windows.Forms" />
                <AssemblyReference AssemblyPath="System.Xml" />
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS.dll" />
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask.dll" />
            </AssemblyReferences>
        </ScriptTaskProject>
    </ScriptProjects>
</Biml>

Thursday, April 21, 2016

Biml Script Task - Test for Echo

Biml Script Task

To aid in debugging, it's helpful to have a "flight recorder" running to show you the state of variables. When I was first learning to program, the debugger I used was a lot of PRINT statements. Verify your inputs before you assume anything is a distillation of my experience debugging.

While some favor using MessageBox, I hate finding the popup window, closing it and then promptly forgetting what value was displayed. In SSIS, I favor raising events, FireInformation specifically, to emit the value of variables. This approach allows me to see the values in both the Progress/Execution Results tab as well as the Output window.

The syntax is very simple, whatever you want recorded, you pass in as the description argument. In this snippet, I'm going to build a string with three items in item in it - the variable's NameSpace, Name and Value.

    bool fireAgain = false;
    string message = "{0}::{1} : {2}";
    var item = Dts.Variables["System::MachineName"];
    Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), string.Empty, 0, ref fireAgain);
When that code runs, it will pull the @[System::MachineName] variable out of the SSIS Variables collection and assign it to item. We'll then fire an information message off with the bits of data we care about.

It seems silly, doesn't it to print the variable's namespace and name there when we asked for it by name from the collection. Of course it is, a nicer, more generic snippet would be

    bool fireAgain = false;
    string message = "{0}::{1} : {2}";
    foreach (var item in Dts.Variables)
    {
        Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), string.Empty, 0, ref fireAgain);
    }
There, that's much better - however many Variables we pass in to the ReadOnly or ReadWrite collection will all get echoed back to the log and output window.

Biml

The following is almost turnkey, you simply need to specify all the variables you want emitted in the log there where it says "List all the variables you are interested in tracking". Follow the pattern and all the Variables you list will get tracked.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="so_36759813">
            <Variables>
                <Variable DataType="String" Name="echoBack">mixed</Variable>
            </Variables>
            <Tasks>
                <Script ProjectCoreName="ST_EchoBack" Name="SCR Echo Back">
                    <ScriptTaskProjectReference ScriptTaskProjectName="ST_EchoBack" />
                </Script>            
            </Tasks>
        </Package>
    </Packages>
    <ScriptProjects>
        <ScriptTaskProject ProjectCoreName="ST_EchoBack" Name="ST_EchoBack" VstaMajorVersion="0">
            <ReadOnlyVariables>
                <!-- List all the variables you are interested in tracking -->
                <Variable Namespace="System" VariableName="MachineName" DataType="String" />
            </ReadOnlyVariables>
            <Files>
                <File Path="ScriptMain.cs" BuildAction="Compile">using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_EchoBack
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            bool fireAgain = false;
            string message = "{0}::{1} : {2}";
            foreach (var item in Dts.Variables)
            {
                Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), string.Empty, 0, ref fireAgain);
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}                </File>
                <File Path="Properties\AssemblyInfo.cs" BuildAction="Compile">
using System.Reflection;
using System.Runtime.CompilerServices;

[assembly: AssemblyVersion("1.0.*")]
                </File>
            </Files>
            <AssemblyReferences>
                <AssemblyReference AssemblyPath="System" />
                <AssemblyReference AssemblyPath="System.Data" />
                <AssemblyReference AssemblyPath="System.Windows.Forms" />
                <AssemblyReference AssemblyPath="System.Xml" />
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS.dll" />
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask.dll" />
            </AssemblyReferences>
        </ScriptTaskProject>
    </ScriptProjects>
</Biml>

We could make this better by using some BimlScript to inspect the package and add all the variables in scope in our ReadWrite list but that's deeper than we're going to go in this post.

Now, if you'll excuse me, it's time to spin Test For Echo.