Go to page:
Prev 1 2 3 Next
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.
(continued)
Click here for a larger image.
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.
Click here for a larger image.
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).
Click here for a larger image.
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:
- Deploy the assembly containing the class in the appropriate place.
- 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:\WINDOWS\Microsoft.NET\Framework\v2.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).
Click here for a larger image.
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.
Click here for a larger image.
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:
- 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.
- 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.
About the Author
Jeff is a software developer specializing in enterprise application integration solutions utilizing BizTalk, SharePoint, WCF, WF, and SQL Server.
Jeff has been developing software with Microsoft tools for more than 15 years in a variety of industries including: military, manufacturing, financial services, management consulting, and computer security.
Jeff is a Microsoft BizTalk MVP.
Jeff spends his spare time with his wife Sherrill and daughter Alexandra.
Go to page:
Prev 1 2 3 Next
All
IE 7
Firefox 2.0
