The Script Task Environment

The Script Task is a powerful tool for customizing packages. Part of that power comes from the fact that the development environment is so advanced and feature rich. The other part of the power comes from the fact that the Script Task uses Visual Basic .NET and gives you complete access to all the resources of .NET, including WinFX libraries and the ever-burgeoning stock pile of shared source code.

The Script Task Editor

To get started with the Script Task, open the designer and create a new Integration Services project or open one of the sample packages in the S15-ScriptTask sample solution. Figure 15.1 shows the Script Task Editor that opens when you double-click on the task in the Control Flow designer.

Figure 15.1. The Script Task Editor

Five properties are available in the Script Task Editor dialog box:

• ScriptLanguage The only language available is Microsoft Visual Basic .NET. When the Integration Services team wrote the Script Task, there were plans to support other languages. Those plans did not materialize, so Visual Basic .NET is the only choice.

• PrecompileScriptIntoBinaryCode This setting specifies that the code should be compiled and stored in the package. This property is set to trUE by default. The compiled code is converted to BASE64 code and stored as part of the package. If you open a package with a Script Task that is precompiled, there is a big block of unintelligible code. If you set it to FALSE, the code is stored in the package, but compiled just in time when the task executes. If it isn't precompiled, there is also a detectable delay when executing the Script Task while the code compiles.

  Tip

  If you ever get the following error: The task is configured to precompile the script, but binary code is not found, you can resolve the issue by simply loading the package into the designer and opening the offending Script Task designer. In general, it's best to set PreCompile to trUE. For running on 64-bit machines, it must be set to trUE because the Script Task cannot "just in time" compile on 64-bit machines.

  This error also sometimes occurs when you attempt to run a package with a Script Task that doesn't successfully compile. The only way to resolve the issue is to fix the code so that it will successfully compile.

  
  

• EntryPoint This is the load point for the Script Task. Although you can change this, there is very little reason to do so. If you do decide to change it, you need to change the name of the encompassing main class to match.

• ReadOnlyVariables This setting specifies which variables the script will open for read-only access.

• ReadWriteVariables This setting specifies which variables the script will open for read and write access.

The last two properties are a simple way to make variables accessible in the script. To access variables, list them in these fields separated by commas. Or, you can use the VariableDispenser, as follows:

Dim vars As Variables Dim var As Variable Dts.VariableDispenser.LockForRead("TestVar") Dts.VariableDispenser.GetVariables(vars) var = vars.Item(0) 

Caution

When adding variable names to the list of ReadOnly and ReadWrite variables, make sure the names have the correct case and there are no spaces between the names and commas. Also, do not use both methods to access variables. If you reserve a variable for access in the Script Task Editor and then attempt to retrieve a variable using the VariableDispenser, you'll receive an error stating that the variable has already been locked.

The Visual Studio for Applications Design Environment

To open the Visual Studio for Applications designer, click on the Design Script button. The designer appears as shown in Figure 15.2.

Figure 15.2. The Visual Studio for Applications design environment

The design environment probably looks a little busy, but it doesn't have to be. Figure 15.2 has as many windows open as possible for the screenshot to show you some of the available features, notably the Object Browser, Project Explorer, and Properties windows. Although these windows are not necessary for writing code, they can be helpful if you're trying to find a particular method or when making changes to the project. As you can see, the main code editor has syntax coloring, automatic indenting, and all the other supporting features found in Visual Studio. Because the environment is quite feature rich, every detail is not covered here. But, a few of the more important Script Taskrelated settings are pointed out next.

Let's take a look at the project first. If you don't have the Project Explorer window open, you can open it from the View menu. In the Project Explorer window, notice the topmost node in the project tree. The name of your project will look similar to the one in Figure 15.2. The Script Task creates a unique name for each Script Task project so that the VSA engine can disambiguate different Script Task code buckets. You shouldn't need to change the name, and the project name doesn't show up in the package. If you're tempted to change the name to make it "prettier" or "consistent," don't do it. It could lead to some odd behavior such as the wrong Script Task opening when a breakpoint is hit, and so forth.

If you right-click on the code node (in Figure 15.2, it is called ScriptMain), there is a menu option for exporting the code to a .vb file. The same option is available on the main File menu for the code currently loaded in the editor. This option allows you to save the code to a file that you can later load into another Script Task. To load a .vb file into the Script Task, right-click on the project and select Add Existing Module. It is useful, although questionable to do this. Arguably, if you're copying code this way, according to the guidelines provided earlier, you should probably be writing a custom task. However, not every case subscribes to the rule, and this feature could be useful for importing a library of useful functions for use in the Script Task. From the project context menu, you can also use the Add menu item to add a new class, and so on.

If you want to add a reference to an assembly, right-click on the References node and select Add Reference. Figure 15.3 shows the Add Reference dialog box that opens.

Figure 15.3. The Add Reference dialog box

Select the assemblies you want to reference and click the Add button. After you've added references, you can browse the objects in the assembly in the Object Browser window or create instances of classes in the assembly in your code.

Caution

If the assembly you want to reference is not visible in the Add Reference dialog box, you'll need to do a little extra work. Ensure that the assembly is in the global assembly cache and then make a copy of the assembly in the following directory:

%WINDIR%\Microsoft.NET\Framework\<latest 2.0 version of .Net>

On the sample machine, it's the following directory:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

Open the Add Reference dialog box again and the assembly should be visible.

This limitation is part of the VSA requirements. The reasons are long and tedious, but basically it's because VSA was built as an application enhancement tool, not a development environment.

Script Task Project Properties

If you select Properties from the Script Task project context menu, the dialog box shown in Figure 15.4 opens.

Figure 15.4. The Script Task project properties dialog box

The Build node of this dialog box allows you to toggle some key settings, as follows:

• Generate Debugging Information Generate the debugging information that is normally bound for the .PDB file. If you have set PrecompileScriptIntoBinaryCode to trUE, the debugging information is also written into the package.

  Caution

  If you are releasing your package to the production environment or concerned about the size of the package, turn this setting off. After you've turned it off, be aware that breakpoints will no longer function until you turn it back on again.

  
  

  Note

  Precompiling a Script Task can increase execution speed because it doesn't need to load every time it executes; this is especially helpful when executing a Script Task in a loop.

  
  

• Option Strict On By Default Visual Basic .NET allows implicit conversions of any data type to any other data type. Data loss can occur when the value of one data type is converted to a data type with less precision or smaller capacity. This setting also disallows late binding to an object of type Object. This setting should always remain selected.

• Option Explicit On By Default This option forces explicit declaration of variables using the Dim statement. This setting should always remain selected.

• Option Compare Binary By Default This setting has to do with comparing text. When selected, all text comparisons are case sensitive.

• Enable Build Warnings This setting allows the environment to emit warnings.

• Treat Warnings as Errors This setting elevates any warnings to error status.

• Define DEBUG Constant Turn on the constant DEBUG.

• Define TRACE Constant Turn on the constant trACE.

• Custom Constants This field allows you to create constant values that will be available in the code.

Interactive Mode

One setting that you won't find in any of the Script Task dialog boxes or anywhere in the environment is the InteractiveMode. Interactive Mode is a package property, but materially affects the Script Task. To see the InteractiveMode property in the designer, open the project properties in the Solution Explorer by right-clicking the project and selecting Properties. The InteractiveMode property is in the Debugging node. By default, the InteractiveMode property on packages is set to FALSE. The package pushes the value of this property to the system variable called System::InteractiveMode. When the designer loads or executes the package, one of the things it does is set the InteractiveMode to trUE so that error dialog boxes and such will be visible.

Note

This variable is available at design time. If you want to see it, look in the Variables window in the designer and make sure the system variables are visible.

Internally, the Script Task references the System::InteractiveMode variable. If InteractiveMode is set to trUE and there are errors in the Script Task code, the Script Task shows a dialog box containing information about the error. That's why the DTS Script Task: Runtime Error dialog box shown in Figure 15.5 shows up in the designer whenever there's an error.

Figure 15.5. The DTS Script Task: Runtime Error dialog box is visible if InteractiveMode is set to trUE

If you take the same package and run it in DTExec, the Script Task does not, by default, show the error dialog box because DTExec does not set InteractiveMode to trUE. This is a good thing. You don't exactly want dialog boxes showing up in the middle of the night on a headless production server in a back closet somewhere. All tasks should honor this setting for this very reason.

The sample package called SafeMessageBox.dtsx in the S15-ScriptTask sample solution has a property expression on the package that always sets the InteractiveMode property to trUE. If you run the package in DTExec, the error dialog box opens just like it does in the designer. Try changing the property expression and see how the behavior changes.

There's a lot to learn about the Script Task environment in general, check the documentation for the designer in Books Online, MSDN, and the Visual Studio documentation.

Setting Breakpoints

The Script Task supports breakpoints, which makes it easier to debug. There are two kinds of breakpoints: those you set in the package Control Flow designer and those you set in the Script Task designer. Like all tasks, you can set breakpoints on the task, but you can also set breakpoints on the code. To set the breakpoint, click on the left margin in the editor; a red dot appears and the designer highlights the adjoining code.

Caution

When debugging a Script Task, you might be tempted to make changes in the code before terminating the package. The changes aren't saved unless you edit the code in the Script Task Editor with the package debugging stopped.