Introduction

Scripting adds more flexibility to SSIS than any other task or component. I am often asked about resources for leaning SSIS scripting. My first recommendation is always Donald Farmer's excellent book: The Rational Guide to Extending SSIS 2005 with Script (Rational Guides).

In this series, I hope to offer some of my thoughts on scripting inside SSIS packages. This is a Level 100 series, but that just means I'm going to assume you're brand new to SSIS and Visual Basic, and provide as much information as I can.

I firmly believe learning about the SSIS Script Task will teach you about SSIS in a way nothing else will. For demonstration purposes, I've created an Integration Services project named ScriptingWithSSIS.

I renamed Package.dtsx to ScriptingWithSSIS.dtsx.

Follow along – it'll be fun!

I'm using SQL Server 2005 Integration Services (SSIS 2005) and Visual Basic.

The Script Task

On the Control Flow, open the Toolbox (ViewàToolbox or Ctrl+Alt+X). Drag a Script Task from the Control Flow Items category onto the Control Flow surface.Once added to the Control Flow, the script task will appear as shown below.To edit, either double-click the script task or right-click and click Edit.The General page of the Script Task Editor opens first:The Name and Description properties are pretty self-explanatory. All I'll say is the Name property sets the name visible on the script task in the Control Flow. The Script page contains a dropdown for Script Language. Don't get too excited, there's only one item in the list in SSIS 2005: Microsoft Visual Basic .Net. (In SSIS 2008, C# is available).

I will not discuss PrecompileScriptIntoBinaryCode or OptimizeScriptExecution in this series, but Matt Masson covers them well in his post: Scripts and assembly caching in 2005. The EntryPoint property specifies the name of the class where execution begins. To be honest, I've never changed this property.

I've changed the next two properties a lot: ReadOnlyVariables and ReadWriteVariables contain comma-separated lists of variable names. As indicated, ReadOnlyVariables contains a list of SSIS variables that are read by the script task but not modified; ReadWriteVariables contains a comma-separated list of SSIS variables that can be modified by the script.

The Expressions page contains a list of script task properties that can be dynamically altered using variables, expressions, or combinations of variables and expressions.

Visual Studio for Applications

On the Script page, click the Design Script button to open the Visual Studio for Applications (VSA) editor. The VSA editor is pre-populated with a template script:

 ' Microsoft SQL Server Integration Services Script Task
 ' Write scripts using Microsoft Visual Basic
 ' The ScriptMain class is the entry point of the Script Task.
 Imports System
 ImportsSystem.Data
 ImportsSystem.Math
 ImportsMicrosoft.SqlServer.Dts.Runtime
 Public Class ScriptMain
       ' The execution engine calls this method when the task executes.
       ' To access the object model, use the Dts object. Connections, variables, events,
       ' and logging features are available as static members of the Dts class.
       ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
       '
       ' To open Code and Text Editor Help, press F1.
       ' To open Object Browser, press Ctrl+Alt+J.
       PublicSub Main()
             '
             ' Add your code here
            '
             Dts.TaskResult = Dts.Results.Success
       End Sub

End Class

There are lots of comments here to get you started. If you delete them (I usually do), you’re left with just the code:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class Script Main
       Public Sub Main()
         Dts.TaskResult = Dts.Results.Success
       End Sub
End Class

This is the ScriptMain class. A class is really a template for an object. When the code is executed, an object is created in RAM from the template. That object in RAM does all the work. In the case of objects inside a script task, the objects do their work within the context of the SSIS package. This means the objects interact with SSIS objects (variables, connections, events, logs) while the package is running.

The ScriptMain Class

There’s not much code here really. Let’s have a look-see.

First, there are the Imports statements. This requires a little history.

Objects contain one or more classes. Classes, in turn, contain one or more subroutines and functions that can be accessed as methods of the object. Classes may also contain properties that define object attributes. The italicized in the preceding sentences are loaded words in the developers’ lexicon.

The .Net Framework is a collection of objects used by developers to perform common tasks. These tasks include, but are not limited to, file system interaction, graphics manipulation, mathematical operations, and data manipulation. Some .Net Framework classes are referenced in a script task by default. You can see them when you open Project Explorer in the VSA editor (ViewàProject Explorer or Ctrl+Alt+L), and then expand the References logical folder.

Once an object is referenced it can be utilized in a class. Remember, a class is a template for an object. It’s not the object itself. The class named ScriptMain is actually a code fragment buried inside the SSIS dtsx file’s XML. The referenced .Net Framework objects can be used in any class in the script task – including ScriptMain; they just need to be imported. Hence the Imports statements.

Next, there’s the declaration of the class: Public Class ScriptMain. Everything between this statement and the End Class statement defines the class proper. This class is named ScriptMain and can be referenced by other objects inside the script task by this name.

The ScriptMain class has one subroutine: Main. It can be referenced internal to the class by calling Main(); and external to the class by calling ScriptMain.Main(). Main contains a single line of code, one that sets the TaskResult property of the script task to Success. It accomplishes this with a call to the Dts object. The Dts object is the script task’s interface to the SSIS package objects. Dts is used to read SSIS variables into the script and write script values back into SSIS variables. In addition, the Dts object facilitates access to SSIS events, connections, and logs. It’s a two-way interface: it also provides feedback to the SSIS package about the disposition of the script task via the Dts.TaskResult.

Conclusion

That’s it for part 1. We’ll interact with variables in part 2.

:{> Andy