Extend the SSIS Script Task with Your Own .NET Class Library

If you’re familiar with SQL Server 2005 Integration Services (SSIS) you’re probably aware that SSIS includes vast improvements over SQL Server 2000 Data Transformation Services (DTS). Among these improvements is extensive .NET support, which comes in many forms. For example, the SSIS Class Library is primarily .NET based, and SSIS Custom Components are developed using .NET tools. In fact, you can customize a SSIS package using .NET. One of the simplest ways is to use the SSIS Script Task.

Using a sample SSIS package (developed with the June 2005 CTP of SQL Server 2005 and Visual Studio 2005), this article explains how to use the Script Task in event handlers and Control Flow.

Sample Application Overview

A SSIS package is divided into three major sections: Control Flow, Data Flow, and events. The following are descriptions of each:

• Control Flow represents the steps the SSIS Package performs.
  
• Data Flow contains the steps a Data Flow Task performs.
  
• Events contain the steps of particular actions that the SSIS Package performs (e.g., stages of execution and errors in execution).

The sample SSIS Package implements Script Tasks in both the Control Flow and events sections, which are very similar processes. (Script Tasks in Data Flow are beyond the scope of this article.) Each of the Script Tasks utilizes a simple class called Operation1. Operation1 displays a MessageBox when you invoke the “RunOperation” function. Normally you wouldn’t display a MessageBox when executing a SSIS package, so the sample acts only to demonstrate some concepts and has no practical value beyond that.

The sample also implements SSIS Package variables to illustrate how Script Tasks utilize variables. Let’s look at how the variables are set up in SSIS before drilling further into the Script Task details.

SSIS Package Variables

Most development tools support the use of variables. In SSIS, variables can be declared at many different levels. Variables declared at the package level (which will be described shortly) are considered global to the SSIS Package.

Declaring a variable is straight-forward. Simply access the SSIS menu Variables option. Figure 1 shows the declaration of a variable called MainObj in the SSIS Package.

Figure 1. Declaration of a Variable Called MainObj

Variable declarations can be primitive types such as integer and string, or other classes implemented as an object type. The sample variable will contain the Operation1 class, so the variable is declared as a System.Object.

Next, learn all about the Script Task.

Setting Up a Script Task

Whether you are adding a Script Task to Control Flow or events, selecting and modifying the Script Task in a SSIS package works just like adding other tasks to your package. Simply select the Script Task and drop in your package. Figure 2 shows the Script Task highlighted in Visual Studio.

Figure 2. The Script Task in Visual Studio

After you select the Script Task, right-click the task and select the Edit option. Figure 3 displays the configure Task options dialog filled in with some appropriate values.

Figure 3: Configure Task Options

As you can see, the SSIS Package variable called “MainObj” is being passed into the Script Task. You must separate multiple variables with commas. (SQL Server 2005 Books Online describes Options in greater detail.)

Add a Script Task to an Event

As previously stated, the sample also uses a Script Task in events. To add a Script Task to an event, you must first select the event containing the Script Task. The sample executes the Script Task inside the OnPreExecute event.

All that remains to finish configuration of the Script Task is to write your VB.NET code. When you press “Design Script”, the application called “Visual Studio for Applications” appears (see Figure 4).

Figure 4: Visual Studio for Applications

At this point, you are ready to write your VB.NET code.

The DTS Object

A key class you will use in Script Task development is DTS. DTS is an instance of the object ScriptObjectModel. ScriptObjectModel allows a Script Task to gather all sorts of information about the Script Task host SSIS Package. The following is a summary of the information you can access from the ScriptObjectModel (DTS class) package:

• DTS Variables allows you to change and retrieve package variables.


• DTS Log allows a Script Task to write messages to the package log.


• DTS TaskResult enumerator values are used for assigning the execution result of the Script Task.

Now let’s move to the heart of the sample application: utilizing a custom .NET class in a SSIS Script Task.

Using a Class in a Script Task

Before you can use a class in a Script Task, you must do the following:

1. Deploy the assembly containing the class in the appropriate place.


2. Reference the class in your Script Task.

To be able to reference an assembly from Visual Studio for Applications, you must place the assembly in the following system folder:

C:WINDOWSMicrosoft.NETFrameworkv2.0.nnnn

In addition, you must deploy the assembly in the Global Assembly Cache (GAC). In my experimentation, I couldn’t get the sample to function without deploying to both places. The sample solution is configured to perform these actions for you (see Figure 5).

Figure 5: Configuration Information

To set the reference to the class, select “Add Reference” from the Project menu option. You’ll see the dialog shown in Figure 6.

Figure 6: “Add Reference” Dialog from the Project Menu

Now you’re ready to start coding.

Coding the Script Task

The following code illustrates how you can utilize a class inside your Script Task:

Dim op As Operation1

Dim msg As String

If Not TypeOf (Dts.Variables(“MainObj”).Value) Is Operation1 Then
  Dts.Variables(“MainObj”).Value = New Operation1
  msg = “Variable was empty”

Else
  msg = “Variable was NOT empty”

End If

There are two keys to understanding how to work with a class within a Script Task:

1. You can access only SSIS Package variables you have configured the Script Task to share using the ReadWriteVariables or ReadOnlyVariables properties. Variables are accessed using the Variables collection on the ScriptObjectModel (DTS) class.


2. Because the SSIS package variable is declared as an object class, the variable can contain any type of class. Thus, make sure that other Script Tasks treat the variable in a similar fashion. The sample performs some type checking to ensure that the variable has been initialized to the appropriate class. In your own implementations, you may want to include some type checking to help with code maintenance.

The following section discusses some other items to consider as you develop your own Script Tasks.

Major Considerations

Your Script Task error-handling approach will depend on a number of things. Ask yourself the following questions when determining your approach:

• Should the error be handled by the Script Task or by the SSIS Package Error Handler?


• Should the error cause the whole package to fail?


• What action must be taken when the error occurs? Should the error be logged?


• How frequently do you expect the error to occur?

The Script Task may be part of a SSIS Package Transaction. Make sure that actions your Script Task performs do not conflict with other parts of the SSIS Package.

Your SSIS package will execute under the configured SSIS account (DTS Server). Therefore, you must consider whether the actions taken by your Script Task have the appropriate authority.

Script Tasks are not your only options if you want to perform something not built into SSIS. Your other options include the following:

• The Execute process allows you to execute a command-line application.


• SQL Server 2005 now includes support for the CLR. You could always build your code into a CLR stored procedure.


• ActiveX Script Task is another option.


• Custom Task Components allow you to build reusable components for all of your SSIS Packages.

Extend Your SSIS Package

A Script Task allows you to extend your SSIS Package, and even your own custom class, using VB.NET. Using the DTS object, a Script Task allows you to access SSIS Package variables and change SSIS Package behavior. However, as you develop your own Script Tasks, you must consider things like transactions and error handling.

Download the Code

To download the accompanying source code for the examples, click here.

About the Author

Jeffrey Juday is a software developer with Crowe Chizek in South Bend, Indiana. He has been developing software with Microsoft tools for more than 12 years in a variety of industries. Jeff currently builds solutions using BizTalk 2004, ASP.NET, SharePoint, and SQL Server 2000. You can reach Jeff at jjuday@crowechizek.com.