Extend the SQL Server 2005 Integration Services 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.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read