Biml - Snippets

I've been working with Biml for a year and a half now. With the Intellisense built into BIDS Helper or Mist itself, I can bang out some code fairly quick. My mental parser isn't too bad either, I can read and generally see what is/isn't set correctly in it. Except for the Script Tasks and Components. Even in Mist, they still kick me in the pants. What's the ProjectCoreName and how does that differ from the ScriptTaskProjectName and should it differ? What's the crazy syntax for escaping my code within the code? Yeah, I don't care anymore.

I no longer care, because I have a snippet. If only I had a Donk! A snippet is like a macro — type some mnemonic keystroke and if you want the snippet, hit Tab. The C# snippet that comes to mind is cw which autocompletes to Console.WriteLine. Man, that'd be helpful for slingin' Biml.

Wait, why haven't I used them? I know you can create custom snippets for .NET so why not one for "XML?" Yeah self, why not? To save you the trouble of arguing with yourself for not being clever, I'm going to tell you to start creating your own snippets, contribute them to bimlscript.com and let's get cranking.

Getting started with snippets

I don't know that you have to, but there's a very handy tool called Snippet Designer that makes it a cinch to create snippets.

Highlight the text you're interested in and in your right click menu, Export as Snippet. You can ignore the Create Snippet..., that's Red Gate's SQL Prompt and won't create the right type of snippet for these file types.

For a script task, I'm just going to assume I'm starting with an brand new Biml file so I've selected everything but that and put it into a snippet. You'll then be presented with a nice little editor so you can use things like anchors and such which I made heavy use of in TextPad's clip library.

I save it out and it goes into a file called C:\Users\bfellows\Documents\Visual Studio 2012\Code Snippets\XML\My Xml Snippets\ScriptTaskCS_2012.snippet

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
      <Title>ScriptTaskCS_2012</Title>
      <Author>admin</Author>
      <Description>
      </Description>
      <HelpUrl>
      </HelpUrl>
      <Shortcut>
      </Shortcut>
    </Header>
    <Snippet>
      <Code Language="xml"><![CDATA[    <ScriptProjects>
        <ScriptTaskProject ProjectCoreName="ST_12345" Name="ST_12345" VstaMajorVersion="0">
            <ReadOnlyVariables>
                <Variable Namespace="System" VariableName="MachineName" DataType="Boolean" />
            </ReadOnlyVariables>
            <Files>
                <File Path="ScriptMain.cs" BuildAction="Compile">using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_12345
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            bool fireAgain = false;
            string message = Dts.Variables["System::MachineName"].Value.ToString();
            Dts.Events.FireInformation(0, "Log MachineName", message, 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;

//
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("AssemblyTitle")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("I <3 @billinkc")]
[assembly: AssemblyProduct("ProductName")]
[assembly: AssemblyCopyright("Copyright @  2015")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
//
// Version information for an assembly consists of the following four values:
//
//      Major Version
//      Minor Version
//      Build Number
//      Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:

[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>
    <Packages>
        <Package Name="BasicScriptTask" ConstraintMode="Linear">
            <Tasks>
                <Script ProjectCoreName="ST_12345" Name="SCR Do Stuff">
                    <ScriptTaskProjectReference ScriptTaskProjectName="ST_12345" />
                </Script>
            </Tasks>
        </Package>
    </Packages>]]></Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

Now when I need a script task, my workflow is

1. Add new biml file
2. Right click, Insert Snippet (Ctrl-K, Ctrl-X)
3. Navigate to My Xml Snippets, select ScriptTaskCS_2012
4. Replace the Name attribute for Package and replace all instances of ST_1235 with something a little more unique

If I click Generate SSIS Package, the biml engine is going to fire up and emit an SSIS package with a script task. How cool is that? Think about how you can leverage snippets and CallBimlScript: Replicate-o-matic, Don't Repeat Your Biml, Callable BimlScript (Caller), etc.

Like this? Joost van Rossum (b|t) just posted Creating BIML Script Component Transformation (rownumber). That's your framework for creating a Script Component, acting as a transform. Add that to your Snippets and now you have an example of each.

For large libraries, you might want to make those CoreNames unique. I'll see if there's an API call for generating a unique name. Also, you can make this into an in-line project script as Scott shows on Creating Script Task Projects inline. The difference between the two approaches boils down to do you want to create shareable, project level tasks or per-package tasks.

I am very excited about integrating snippets into my biml workflow and I hope this has opened your eyes to another means for speeding your development.