Script Tasks and Custom Libraries

Script Tasks are valuable tasks for handling custom functionality because code can be written within the task. But when you want to use the same code over and over again, why rewrite or cut and paste code for each Script Task? A good developer knows that code re-use makes developing code efficient and easier to maintain. That is why it is a good practice to build custom libraries so that they can be used as functional components as well as building blocks within Script Tasks.

Important 

Code reusability is also a great case for writing custom components. However, your code may not be suitable for a custom component because of its unique variability, or perhaps your company policies prevent registering such components on your servers. In these cases, the Script Task is probably the right solution, and with custom libraries, you are still able to consolidate redundant code.

User-Defined Variables

Variables are primarily used as configuration settings within SSIS during runtime. Using variables allows packages to be configured dynamically, rather than having to hard-code settings in the package. There are system variables that are defined by SSIS, and user-defined variables that developers build for their own settings (which a package may use dynamically). Creating user-defined variables and then using them from within Script Task variables is very powerful for dynamic package settings. User-defined variables are created within BIDS and can be tailored to hold many different types of data types.

Let’s now walk through the first steps for building a custom script within the Script Task and defining user-defined variables to control how it will function. Start BIDS and create a new project named UserVariables, as shown in Figure 2-1.

Figure 2-1: Starting a new project

Double-click the package.dtsx package within Solution Explorer. The design view for the package will come up. Now, the important window is the Variables window, so if you do not see it, look on the toolbar and click View Other Windows Variables.

When the Variables window is visible (as shown in Figure 2-2), you will see that there are four columns that show up by default:

• Name- This column is the name of the variable, and each variable must be unique within its scope.

• Scope- This column represents where the variable is scoped. You can scope a variable to a package, a container, or a task. If you were to have a variable scoped to a task, no other task would be able to see the variable. When you scope a variable, you cannot change the scope without deleting it and re-creating it.

• Data Type- This column shows the data type for the variable.

• Value- This column shows the user-defined initial value for the variable.

Figure 2-2: Four default columns showing in the Variables window

There is also a toolbar that enables you to add or delete variables, show system or user variables, and view additional columns or attributes about the variables. There are two additional columns you can add if it proves useful to you:

• Namespace- This generally has very little use. It essentially allows you to qualify a variable with something other than User: or System:.

• Raise Event if Variable Value Changes- This is set to false by default. However, when set to true, the OnVariableValueChanged event handler will be executed each time the value of this variable is changed.

To create a variable, click the first icon (from the left) on the toolbar. This creates a new variable with default attributes. When you look at the scope of the new variable, you will notice that it has a Package scope. This is because you have not added any tasks or containers.

To create a variable scoped within an example Script Task, go back to the control flow of the package and drag over a Script Task. In the Variables window, with the added Script Task selected, add a new variable. Note that the scope of the new variable is now Script Task.

Let’s create a variable that allows a Script Task to be used as a configurable resource for hitting HTTP sites. Change the Name of the new variable to varHttp, change the Data Type to String, and clear out the Value so that it will be represented in code as an empty string. Also, create a variable called varSaveFile of type String.

Retrieving Variables with Code

To now expand the example, let’s use the Script Task to show a variety of ways to read a variable. There are two specific ways that it can be used: the easiest way is the Variables object, and the other is the VariableDispenser object. The Variables object requires less code, but is slightly less efficient and requires more interaction in the GUI. The VariableDispenser locks variables for a slightly shorter period of time.

Let’s start by double-clicking the Script Task that you created earlier to edit the task. For this example, use the Variables object, which requires one additional step in the GUI. The Script Task must know which variables are available to read and which variables are available to read and write. Select Script from the left pane, as shown in Figure 2-3. The EntryPoint property is set to ScriptMain, which is also the default class that is built and executed when the task is started. The ReadOnlyVariables and ReadWriteVariables properties let the Script Task know which variables will be passed into the task’s code. In this example, varHttp has been added as a value to the ReadOnlyVariables property. More than one variable can be added by using commas to separate each of the variables entered, if needed.

Figure 2-3: Script Task Editor

A common question that we see is what language you will be able to use in the Script Task and Component. Both of these tasks can only use Visual Basic .NET, since the scripting engine in SSIS is built on top of Visual Studio for Applications, which only supports VB. This is illustrated in the ScriptLanguage drop-down list box in the Script Task or Component. More languages are likely to come in future major releases of SSIS, but this will require a shift from Visual Studio for Applications or an enhancement to that Visual Studio for Applications platform.

To view the Script Task’s code, click on the Design Script button on the Script Task Editor window, found at the bottom of the screen shown in Figure 2-3. Microsoft Visual Studio for Applications opens, as shown in Figure 2-4. Remember that the ScriptMain class is automatically set as the EntryPoint, and Main() is already added to be the first routine to be executed.

Figure 2-4: Viewing the code in Microsoft Visual Studio for Applications

To access a variable with the Variables object, you can use the following code. This code does the same things as shown Figure 2-4 with a slightly easier coding tactic. All this code is doing, though, is simply propagating an SSIS variable to be used as a variable in Visual Basic .NET.

  Public Sub Main()    Dim strHTTP As String = Dts.Variables("varHTTP").Value.ToString     Dts.TaskResult = Dts.Results.Success End Sub 

The other option for letting a Script Task know about read-only, user-defined variables is to use the Dts.VariableDispenser.LockOneForRead method, as shown in Figure 2-4. This method could be considered a late-binding approach because the task is only aware of the variable at runtime, and nothing in the task itself must be configured to make the code aware of the variable. Previously, the variable was set up through the Script Task Editor, which is an early binding approach.

If you use the late-binding approach, then a variable collection must be created that will be passed ByRef to the LockOneForRead method. This will hold the user-defined varHttp variable, which was created earlier. Also required is the name of the user-defined variable or key that identifies the item in the collection to be added. At this point, you can retrieve the value of the first item of the collection (indexed by 0), or use the name of the user-defined variable.

Accessing Variables in the Data Flow

Veering from this example slightly is how to access variables from the Script Component in the data flow. This is slightly more complex than accessing variables in the Script Task, but the same concepts apply. When you access a variable from a Script Component, the variable’s values are only available to be changed during select periods of the data flow’s execution.

The first minor difference to note is that when you list the variables that you want to pass into the component of the data flow, you must not have spaces between each variable name (at least as of SQL Server 2005 SP2). Spaces are forgiven between variables in the Script Task.

The coding syntax also changes slightly in the Script Component. In the Script Component, you’ll reference the variable with Variables.VariableName as shown in the following code. The ToString is not required in the following code and is only used as a good coding practice in order to be very explicit.

  Public Overrides Sub CreateNewOutputRows()     Dim strHTTP As String = Variables.varHttp.ToString     'Some other code here… End Sub 

Another distinction is that variables can only be written to in the PostExecute() subroutine. This is for performance reasons to keep the variable from being locked constantly as each row goes through the component. The PostExecute subroutine is only called after all rows have gone through, and you can write to a variable at that point to show how many rows you transformed.