Script and ActiveX Tasks

The Script task allows you to access the Microsoft Visual Studio for Applications (VSA) environment to write and execute scripts by using the VB.NET language. The ActiveX Script task allows you to continue using your legacy ActiveX scripts from SQL Server 2000. There are some key advantages to using the Script task over the ActiveX Script task. The Script task gives you these extra functional advantages:

• An integrated Visual Studio design environment with Intellisense.

• You can easily pass parameters into the script.

• You can easily add breakpoints into your code.

• You can pre-compile your script into binary format for a speed advantage.

The Script task is configured through the Script page in the Script Task Editor (shown in Figure 3-17). Even though there's a ScriptLanguage option that lets you select what .NET language you'd like to use, only VB.NET is available in this release of SQL Server. If the PreCompileScriptIntoBinaryCode option is set to true, the code will compile and speed up your runtime of the task significantly. The side effect of this, though, is that your package will be larger than its uncompiled state.

Figure 3-17

The EntryPoint option sets the entry point class that the task will call. The ReadOnlyVariables and ReadWriteVariables options allow you to pass SSIS variables into the script. The former is for read-only parameters, and variables can be written to the latter. With each option, you can separate each SSIS variable by commas. Once you're ready to write the script, click Design Script.

When you click Design Script, the Visual Studio environment opens, which lets you use advanced debugging tactics, breakpoints, and Intellisense. The following example shows you how to call the TaskName system variable that was passed into the script. The script firsts sees if the variable exists and then pops up a message box with the task name.

 Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain     Public Sub Main()         Dim variables As Variables         If Dts.Variables.Contains("TaskName") = True Then             Dts.VariableDispenser.LockOneForRead("TaskName", variables)             Dim TaskName As Object = variables("TaskName").Value         End If         MsgBox("You are in the task: " & CStr(variables("TaskName").Value))         Dts.TaskResult = Dts.Results.Success     End Sub End Class 

The results of the task execution would look like Figure 3-18.

Figure 3-18

With regard to scripting, take a look at the ActiveX Script task. This is in SSIS largely for backward compatibility with SQL Server 2000 DTS. You'll find that it's not a very advanced task, but it does have some advantages. Like the Script task, the task is configured through the Script page. As in its SQL Server 2000 predecessor, it supports more languages than just VBScript with the Language option. It will support whatever language is installed on the SSIS machine, such as JScript in addition to VBScript. The EntryMethod option specifies the class that you'd like the script to execute. Click the ellipsis button next to the Script option to type in your script. You can also click Browse to locate the script file.

Note

Browsing to a script will overwrite any script that you already have in the Script option.