Chapter 11

Coding Custom Script Tasks

When you create a new SQL Server Integration Services (SSIS) package, you may find yourself wanting some functionality that the built-in tasks cannot accomplish. This situation is where the Script Task comes into play. This task can accomplish anything that can be done with any .NET programming. Interestingly, the Script Task is not a scripting language at all. In SSIS, you can use VB.NET or C# to write complete coding solutions to perform just about any task your imagination can come up with.

When you drag over a Script Task and double-click it to open the Script Task Editor, you first see three nodes listed on the left: Script, General, and Expressions, as shown in Figure 11-1. Expressions are discussed later in this book (see Lesson 14). This lesson focuses on the General and Script nodes.

Figure 11-1

Under the General node, you see the name and description of the Script Task. This does not affect the code in the script; it is used for ease of reference when viewing the tasks in the Control Flow. The name shows on the tasks in the Control Flow. The description is usually a longer line of text describing the purpose of the Script Task. It is a best practice to always change the values of these fields to values that will make it easy for anyone to see and understand the function of the task.

In the Script node you have four properties to set:

• The first is the ScriptLanguage. VB.NET is used for all of the examples in this lesson.
• EntryPoint is the next property. This is the location in the code where the task looks to execute the code first. Generally, this is left at Main because Main is automatically set up in the built-in starting script.

The next two properties enable you to list the variables from the package that you can use in the Script Task code:

• ReadOnlyVariables are variables that you want to use in the Script Task code, but you do not want the values of the variables edited.
• The ReadWriteVariables are variables used in the Script Task that can have their values changed, meaning you can change the values of the variables to be used in the package after the Script Task completes.

At the bottom of this node, you can see a button labeled Edit Script. The default script language is C#.

When you click the Edit Script button, it opens the Visual Studio Script Editor. If this is your first time opening the script editor, you see the first-time configuration window. After the environment is configured, you see a screen similar to the one shown in Figure 11-2, which is very similar to the Visual Studio coding environment. Developers should feel right at home with this interface.

On the right hand side you will see the ScriptMain.Vb window. This window contains the beginning code needed to start writing your script. This Main section is where you will write most of your code.

Most Script Tasks use ReadOnlyVariables, ReadWriteVariables, or a combination of both. As mentioned earlier in the lesson, to get a variable to be available in the Script Task, you need to add it to the ReadWriteVariables or ReadOnlyVariables in the Script node on the Properties screen of the task. One of the most common tasks is changing a filename based on the conditions in a Script Task. You can accomplish this by passing in a ReadWriteVariable with the filename and using the VB.NET code to change the variable.

First, you have to add the variable name to the ReadWriteVariables variable property. When you click the ReadWriteVariables line, an ellipsis appears on the right. Click this ellipsis button to see the list of all variables in the package, as shown in Figure 11-3. Place a check next to the variable name and click OK. Now the variable shows in the variable property as User::Variable name. You can now use this variable in the Script Task code.

Figure 11-2

Figure 11-3

Now you can click the Edit Script button and write some code to change the value of the variable. Change the variable to “newvalue” and then make a popup box appear showing the value of the variable. Write this code below the public sub main starting code. Remember the entry point was left at Main in the properties of the Script Task. The following code shows how to accomplish this function:

Dts.Variables(“strFileName”).Value = “newvalue”
MsgBox(Dts.Variables(“strFileName”).Value)

Figure 11-4

Notice that the variable is called using the string literal name of the file and it is case-sensitive. Use the value property of the variable to set it to a “newvalue”. The next line is the typical message box in VB.NET. This causes a popup box to appear showing the value of the variable. If the value is set correctly, you see a popup box as shown in Figure 11-4.

You can use two types of variables in Script Tasks. The one just shown is the variable from the package. However, you can also create variables in the Script Task just as you would in a regular .NET application. This variable is different than the package variable and is not used outside of the Script Task. You create this variable with a Dim statement. The value of the variable is changed directly and does not require the use of the DTS.Variables() method. The following code shows how to create a variable, give it a value, and then pop up a message box with the value of the variable:

Dim strInternal As String
strInternal = “test”
MsgBox(strInternal)

Figure 11-5

This code causes a popup box to appear, as shown in Figure 11-5. Notice the value of test was saved in the variable value and then shown in the popup box. Again, you did this directly without using the Dts.Variables() method. The variable cannot be called by the package directly.

Keep in mind that you can have variables in your package with the same name as the variables in your Script Task. These variables do not pass values between the Script Task and the package. To pass values from the script variables to the package variables you need to set the package variable value to the value of the script variable. The following code shows how to do this:

Dts.Variables(“strFileName”).Value = strInternal

Another common function of Script Tasks is the creation of “if then” statements. You can use these statements to make decisions based on certain values. A common use for this functionality is to have an Execute SQL Task to count values from a table and pass that value into a variable. For example, say you want to see if a filename exists in an auditing table to determine if the file should be used. The Execute SQL Task saves the count value to a variable called intAuditCount. This value is compared with the “if then” statement and then used in further code. The following code shows an example of the “if then” statement:

If Dts.Variables(“intAuditCount”).Value > 0 Then
   ‘code for the file found in the audit table
Else
   ‘code for the file not found in the audit table
End If

Altering connections is another common task that Script Tasks can perform. First, the connection must exist in the connection manager of the package. Connection managers are explained in Lesson 6. Assume the connection is named AdventureWorks2012. To alter this connection, use the Dts.Connections() method. The following code shows an example of changing a connection string. Notice the literal name is in parentheses and double quotes, and is case-sensitive. The ConnectionString property of the connection follows. You can then set the connection string to be equal to the needed string. This enables you to change the connection during the package run time.

Dts.Connections(“AdventureWorks2012”).ConnectionString = _
“Data Source=localhost;Initial Catalog=AdventureWorks2012;” + _
“Provider=SQLNCLI10.1;Integrated Security=SSPI;”

Checking for the existence of a file is a common need in SSIS packages. To perform this function, you must import the System.IO into the Script Task. Simply add the line Imports System.IO after the last Import line at the top of the Script Task code. You must create two variables on the package: a string variable to hold the filename and a boolean variable to set to true if the file exists and false if it does not exist. Name them strFileName and bolFileExist. The code would then be:

If File.Exists(Dts.Variables(“strFileName”).Value) Then
Dts.Variables(“bolFileExist”).Value = True
Else
Dts.Variables(“bolFileExist”).Value = False
End If

Checking to see if a file is in use is another common task that can be performed with a Script Task in SSIS. Use the variables strFileName as the filename and bolFileInUse as the boolean variable and set this to true if the file is in use. The code would be:

Try
File.SetLastAccessTime(Dts.Variables(“strFileName”).Value, Today)
Catch e As Exception
Dts.Variables(“bolFileInUse”).Value = True
End Try

Notice that the code is catching an exception. The Script Task attempts to set the last access date of the file to today’s date. If this process fails, the exception will set the boolean variable to true to indicate that the file is in use. Before running this code, you may want to use the previous code that checks if a file exists to determine whether the file does exist. That ensures that you don’t catch an exception because the file does not exist when you really want to catch it because the file is being used.

After these boolean variables are set with the Script Task, you can use the expression on the precedence constraints coming from the Script Task to determine which direction the Control Flow should go. You may have two precedence constraints leaving the Script Task, both with expressions on them. One precedence constraint expression checks for a value of true and the other checks for false. The value of the boolean variable will be evaluated, and the Control Flow will continue down the proper precedence constraint line.

Now you can use the Script Task to perform complicated decision making based on the values of the variables in the package and the values of the variables in the script. You can write these values into the ReadWriteVariables and use them later in the package. The Script Task is a very powerful component that enables developers to write complex code components to perform functions that might not exist in the built-in tasks in SSIS.

Try It

In this Try It, you create a Script Task that changes the value of a variable based on the value of another variable. After completing this lesson you will understand how to use the Script Task to make changes to a package.

You can download Lesson11.dtsx and the sample code from www.wrox.com.

Lesson Requirements

You need to create two variables called intVar and strVar. You want to check the value of the intVar, and if it is above 10, you want to display the word “Big”. If the value is 10 or less, you want to display “Small”. The message box should display the value of the variable strVar and not the literal string of the words.

Hints

• You need only one message box code line in the Script Task.
• Set the value of the strVar to “Big” or “Small”.

Step-by-Step

Figure 11-6

If Dts.Variables(“intVar”).Value > 10 Then
Dts.Variables(“strVar”).Value = “Big”
Else
Dts.Variables(“strVar”).Value = “Small”
End If

MsgBox(Dts.Variables(“strVar”).Value)

Figure 11-7

Figure 11-8