Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Scripting in SSIS – Part 1

By Andy Leonard,

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.

New Project

I renamed Package.dtsx to ScriptingWithSSIS.dtsx.

Solution Explorer

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.SSIS ToolboxOnce added to the Control Flow, the script task will appear as shown below.Script Task on designerTo edit, either double-click the script task or right-click and click Edit.Script Task selectedThe General page of the Script Task Editor opens first:Script Task EditorThe 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).Script Task Editor

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.

Project Explorer

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

Total article views: 12610 | Views in the last 30 days: 23
 
Related Articles
BLOG

Using SMO Transfer Class to Script Database Objects

I’ve spent some time trying to get the SMO Transfer class to bend to my will. I want to script...

ARTICLE

SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers

SQL Server MVP and SSIS guru, Andy Leonard, discusses Integration Services and some of the tasks and...

FORUM

SSIS script object

Script object Design Script does not open up script editor

BLOG

SSIS : Using the VariableDispenser object within Script Components

 I have become accustom to modifying package variables within my Script Task using the VariablesDi...

BLOG

SSIS : Using the VariableDispenser object within Script Components

 I have become accustom to modifying package variables within my Script Task using the VariablesDi...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones