SSIS Script Task

USING THE SCRIPT TASK

Now that you have a good overview of the scripting environment in SSIS, it’s time to dig into the Script Task and give it a spin. The Script Task was used heavily to demonstrate how the SSIS scripting environment works with Visual Studio and during the execution of a package. Generally, anything that you can script in the .NET managed environment that should run once per package or code loop belongs in the Script Task. The Script Task is used in the Control Flow of a package. Script Tasks are extremely useful and end up being the general-purpose utility component if the desired functionality is not available in the out-of-the-box Control Flow tasks.

Script Task In SSIS

Configuring the Script Task Editor

An earlier look at the Script Task Editor pointed out that two selections are available for the scripting language, but there are other options as well. Drop a Script Task on the Control Flow surface to display the Script Task Editor shown in below screen shot.

Here are the four properties on the Script tab to configure the Script Task:

• ScriptLanguage: This property defines the .NET language that will be used for the script. As demonstrated earlier, VB and C# are your two options.
• EntryPoint: This is the name of the method that will be called inside your script to begin execution.
• ReadOnlyVariables: This property enumerates a case-sensitive, comma-separated list of SSIS variables to which you allow explicit rights to be read by the Script Task.
• ReadWriteVariables: This property enumerates a case-sensitive, comma-separated list of SSIS variables to which you allow the script to read from and write to.

All scripts are precompiled by default, which improves performance and reduces the overhead of loading the language engine when running a package.

The second tab on the left, General, contains the task name and description properties.

The final page available on the left of this dialog is the Expressions tab. The Expressions tab provides access to the properties that can be set using an expression or expression-based variable. (See Using Variables, Parameters, and Expressions Topic for details about how to use expressions and variables.) Keep in mind that changing the ScriptLanguage property at runtime is neither possible nor desirable even though it is listed as a possibility in the Expression Editor.

Once the script language is set and the script accessed, a project file with a class named ScriptMain and a default entry point named Main() is created. As a reminder, an example of the Main() function    is provided here  without the

The code provided includes the statement to set the TaskResult of the Dts object to the enumerated value for success. The Script Task itself is a task in the collection of tasks for the package. Setting the TaskResult property of the task sets the return value for the Script Task and tells the package whether the result was a success or a failure.

By now, you have probably noticed all the references to Dts. What is this object and what can you do with it? This question is answered in the next section, as you peel back the layers of the Dts object.

The Script Task Dts Object

The Dts object is actually a property on your package that is an instance of the

Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel

class. The Dts object provides a window into the package in which your script executes. Although you can’t change properties of the package as it executes, the Dts object has seven properties and one method that allow you to interact with the package. The following is an explanation of these members:

• Connections: A collection of Connection Managers defined in the package. You can use these connections in your script to retrieve any extra data you may need.
• Events: A collection of events that are defined for the package. You can use this interface to fire off these predefined events and any custom events.
• ExecutionValue: A read-write property that enables you to specify additional information about your task’s execution using a user-defined object. This can be any information you want.
• TaskResult: This property enables you to return the success or failure status of your Script Task to the package. This is the main way of communicating processing status or controlling flow in your package. This property must be set before exiting your script.
• Transaction: Obtains the transaction associated with the container in which your script is running.
• VariableDispenser: Gets the VariableDispenser object, which you can use to retrieve variables when using the Script Task.
• Variables: A collection of all the variables available to any script.
• Log: You can use this method to write to any log providers that have been enabled.

The next few sections describe some of the common things that the Script Task can be employed to accomplish.

Accessing Variables in the Script Task

Variables and expressions are an important feature of the SSIS road map. In the following scenario, “variables” describe objects that serve as intermediate communication mediums between your Script Task and the rest of your package. As discussed in Error and Event Handling Topic, variables are used to drive the runtime changes within a package by allowing properties to infer their values at runtime from variables, which can be static or defined through the expression language.

The common method of using variables is to send them into a Script Task as decision-making elements or to drive downstream decisions by setting the value of the variable in the script based on some business rules. To use a variable in a script, the variable must be locked, accessed, and then unlocked. There are two ways of doing this: explicitly and implicitly.

The explicit method uses the VariableDispenser object, which provides methods for locking variables for read-only or read-write access and then retrieving them. At one time, this was the standard way of accessing variables in scripts. The explicit locking mechanism allows control in the Script Task to keep two processes from competing for accessing and changing a variable. This will also reduce the amount of time the variable is locked, but forces the developer to write code.

To retrieve a variable using the VariableDispenser object, you have to deal with the implementation details of locking semantics, and write code like the following  

The implicit option of handling variables is the alternative to manually locking, using, and unlocking the variable. This option is best when you simply want the variables that you are using in a Script Task to be locked when you are reading and writing; you don’t want to worry about the locking implementation details. The Variables collection on the Dts object and the ReadOnlyVariables and ReadWriteVariables properties for the Script Task allow you to set up the implicit variable locking. The only constraint is that you have to define up front which variables going into the Script Task can be read but not written to versus both readable and writable.

The ReadOnlyVariables and ReadWriteVariables properties tell the Script Task which variables to lock and how. The Variables collection in the Dts object is then populated with these variables. This simplifies the code to retrieve a variable, and the complexities of locking are abstracted, so you have to worry about only one line of code to read a variable

It is safest to use the fully qualified variable name, such as User::SomeStringVariable. Attempting to read a variable from the Variables collection that hasn’t been specified in one of the variable properties of the task will throw an exception. Likewise, attempting to write to a variable not included in the ReadWriteVariables property also throws an exception. The biggest frustration for new SSIS developers writing VB script is dealing the error message.

The resolution is simple. Either the variable name listed in the Script Task Editor or the variable name in the script doesn’t match, so one must be changed to match the other. It is more confusing for the VB developers because this language is not case sensitive. However, the SSIS variables are case sensitive, even within the VB script.

NOTE Although Visual Basic .NET is not case sensitive, SSIS variables are.

Another issue that happens occasionally is that a developer can create more than one variable with the same name with different scopes. When this happens, you have to ensure that you explicitly refer to the variable by the fully qualified variable name. SSIS provides a Select Variables dialog, shown in below screen shot, that enables selection of the variables.

Fortunately, the Script Task property for the ReadOnlyVariables or ReadWriteVariables is auto-filled with the fully qualified names: User::DecisionIntVar and User::DecisionStrVar. This reduces most of the common issues that can occur when passing variables into the Script Task. All this information will now come in handy as you walk through an example using the Script Task and variables to control SSIS package flow.

Example: Using Script Task Variables to Control Package Flow

This example sets up a Script Task that uses two variables to determine which one of two branches of Control Flow logic should be taken when the package executes. First, create a new SSIS package and set up these three variables:

Then drop three Script Tasks on the Control Flow design surface so that the package looks like Screen shot shown below.

There are two variables, DecisionIntVar and DecisionStrVar, that represent the number of rows determined to be in a file and the file extension, respectively. These variables are fed into the Script Task. Assume that these values have been set by another process. Logic in the Script Task will determine whether the package should execute the CRD File Path Script Task or the TXT File Script Task. The control of the package is handled by the other external variable named HappyPathEnum. If the value of this variable is equal to 1, then the TXT File Script Task will be executed. If the value of the variable is equal to 2, then the CRD File Path Script Task will be executed. Open the Script Task Editor for the Parent Script Task to set up the properties (see the below screen shot).

Set the Script Language and then use the ellipsis button to bring up the variable selection user interface . Select the variables for ReadOnlyVariables and ReadWriteVariables separately if you are using this dialog. You can also type these variables in, but remember that the variable names are case sensitive. As shown in below figure, note the ordinal positions of the variables for this example.

Keep this script simple for demonstration purposes. The most important parts are the retrieving and setting of the variables. This code uses the named references for the variables for the retrieval of the variable values:

The setting of variables uses the same syntax but reverses the assignment. The code that should be pasted into the Main() function of the ScriptMain class will evaluate the two variables and set the HappyPathEnum variable.

Frequently Asked SSIS Interview Questions & Answers

To alter the flow of the package, set the two precedence constraints in the package hierarchy to be based on a successful completion of the previous Script Task and an expression that tests the expected values of the HappyPathEnum variable. This precedence specifies that the Control Flow should go in a direction only if the value of an expression tests true. Set the precedence between each Script Task to one of these expressions going to the TXT and CRD tasks, respectively:
@HappyPathEnum == 1
Or
@HappyPathEnum == 2
A sample of the precedence between the Script Task and the TXT File Script Task should look like below the screen shot.

Now, to give the package something to do, simply retrieve the value of the set variable in each child Script Task to provide visual proof that the HappyPathEnum variable was properly set. Add this code into the Main() function of each child Script Task (make sure you set the message to display TXT or CRD for each associated Script Task)

To see how this works, set the value of the User::DecisionIntVar variable to a positive integer number value, and the User::DecisionStrVar variable to either txt or crd, and watch the package switch from one Control Flow to the other. If you provide a value other than txt or crd (even “txt” with quotes will cause this), the package will not run either leg, as designed. This is a simple example that you can refer back to as your packages get more complicated and you want to update variables within a Script Task. Later in this Scripting in SSIS Topic, you’ll see how the Script Component accesses variables in a slightly different way.

Connecting to Data Sources in a Script Task

A common use of an ETL package is to grab a connection to retrieve decisionmaking data from various data sources, such as Excel files, INI files, flat files, or databases like Oracle or Access. This capability allows other data sources to configure the package or to retrieve data for objects that can’t use a direct connection object. In SSIS, with the Script Task you can make connections using any of the .NET libraries directly, or you can use connections that are defined in a package. Connections in SSIS are abstractions for connection strings that can be copied, passed around, and easily configured.

The Connections collection is a property of the Dts object in the Script Task. To retrieve a connection, you call the AcquireConnection method on a specific named (or ordinal position) connection in the collection. The only thing you really should know ahead of time is what type of connection you are going to be retrieving, because you need to cast the returned connection to the proper connection type. In .NET, connections are not generic. Examples of concrete connections are SqlConnection, OleDbConnection, OdbcConnection, and the OracleConnection managers that connect using SqlClient, OLE DB, ODBC, and even Oracle data access libraries, respectively. There are some things you can do to query the Connection Manager to determine what is in the connection string or whether it supports transactions, but you shouldn’t expect to use one connection in SSIS for everything, especially with the additional Connection Managers for FTP, HTTP, and WMI. Assuming that you’re up to speed on the different types of connections covered earlier in this Topic, it’s time to look at how you can use them in everyday SSIS Script Tasks.

Example: Retrieving Data into Variables from a Database

Although SSIS provides configurable abilities to set package-level values, there are use cases that require you to retrieve actionable values from a database that can be used for package Control Flow or other functional purposes. While this example could be designed using other components, we’ll use this to show how to access variables from a script.
For example, some variable aspect of the application may change, like an email address for events to use for notification. In this example, you’ll retrieve a log file path for a package at runtime using a connection within a Script Task. The database that contains the settings for the log file path stores this data using the package ID. You first need a table in the AdventureWorks database called SSIS_SETTING. Create the table with three fields, PACKAGE_ID, SETTING, and VALUE, or use this script

You can find the package identifier in the properties of the package.

Then create an SSIS package with one ADO.NET Connection Manager to the AdventureWorks database called AdventureWorks and add a package-level variable named LOGFILEPATH of type String. Add a Script Task to the project and send in two variables: a read-only variable System::PackageID and a readwrite variable User::LOGFILEPATH. Click the Edit Script button to open the Script project and add the namespace System.Data.SqlClient in the top of the class. Then add the following code to the Main() method

In this code, the package ID is passed into the Script Task as a read-only variable and is used to build a T-SQL statement to retrieve the value of the LOGFILEPATH setting from the SSIS_SETTING table. The AcquireConnection method creates an instance of a connection to the AdventureWorks database managed by the Connection Manager and allows other SqlClient objects to access the data source. The retrieved setting from the SSIS_SETTING table is then stored in the writable variable LOGFILEPATH. This is a basic example, but you use this exact same technique to retrieve a recordset into an object variable that can be iterated within your package as well. Let’s do that now.

Example: Retrieving Files from an FTP Server

A frequent source of data to use in a solution is files retrieved from an FTP server. SSIS provides an FTP Connection Manager and FTP Task to assist in this function. To use these objects, you need to know what file you want to retrieve from the FTP server. But what do you do if you don’t know what the file name is, and you just want to pull everything from the server? This is a perfect use for a Script Task. The final package that we will create can be seen in below the screen shot.

Begin by adding an FTP Connection Manager that points to your FTP server and a Script Task to your package. The Script Task will use one read/write variable, named FileList, to pass back the list of files to be transferred from the FTP server. We can then add the following code inside the script

This code connects to the FTP server and returns a list of the files available for download. To allow the information to be used in a Foreach Loop Container, the file names are put into an ArrayList and then into the FileList variable.

Our next step is to add the Foreach Loop Container, which will enumerate over the variable FileList. Each iteration will store the name of the file in the FileName variable. Finally, an FTP Task placed inside of the container will use the FileName variable as the source variable to retrieve the file.

With just a few steps, we were able to find out what files are available on the server and download all of them. Next we will look at saving information to an XML file.

Example: Saving Data to an XML File

Another common requirement is to generate data of a certain output format.When the output is a common format like Flat File, Excel, CSV, or other database format, you can simply pump the data stream into one of the Data Flow Destinations. If you want to save data to an XML file, the structure is not homogeneous and not as easy to transform from a column-based data stream into an XML structure without some logic or structure around it. This is where the Script Task comes in handy.

NOTE If you want to parse out the XML file and put the data into a destination, a Script Component could also be used here.

The easiest way to get data into an XML file is to load and save the contents of a data set using the method WriteXML on the data set. With a new Script Task in a package with an ADO.NET connection to AdventureWorks, add a reference to System.Xml.dll and then add the namespaces for System.Data.SqlClient, System.IO, and System.Xml. Code the following into the Script Task to open a connection and get all the SSIS_SETTING rows and store them as XML:

NOTE See the previous example for the DDL to create this table in the AdventureWorks database.

There is not much to note about these results, except that the file is in XML format:
If you need more control of the data you are exporting, or you need to serialize data, you need to use the Script Task in a different way. The next example provides some tips on how to do this.

Example: Serializing Data to XML

In the last example, you looked at simply dumping database data into an XML format by loading data into a DataSet and using the WriteToXML method to push the XML out to a file stream. If you need more control over the format, or the data is hierarchical, using .NET XML object-based serialization can be helpful. Imagine implementations that pull data from flat-file mainframe feeds and fill fully hierarchical object models. Alternatively, imagine serializing data into an object structure to pop an entry into an MSMQ application queue. This is easy to do using some of the same concepts.

Create another package with a connection to the AdventureWorks database; add a Script Task with a reference to the System.Data.SqlClient namespace. Use the data from the previous example and create a class structure within your ScriptMain to hold the values for each row of settings that looks like this 

This class will be filled based on the data set shown in the last example. It is still a flat model, but more complex class structures would have collections within the class. An example would be a student object with a collection of classes, or an invoice with a collection of line items. To persist this type of data, you need to traverse multiple paths to fill the model. Once the model is filled, the rest is easy.

First, add the namespaces System.Xml.Serialization, System.Collections.Generic, System.IO, and System.Data.SqlClient to your Script Task project. A simple example with the SSIS_SETTING table would look like this 

NOTE Keep in mind that while this example uses a connection directly in the code, you can also use an SSIS Connection Manager, as shown in the FTP example. Using a connection manager will make your package more portable to a production environment if you use parameters or configurations.

The StreamWriter here just gets an I/O stream from the file system to use for data output. The XmlSerializer does the heavy lifting and converts the data from the object format into an XML format.

The only trick here is understanding how to deal with the Generic List or the collection of SSISSetting objects. This is handled by using the override, whereby you can add the specific types to the serializer along with the List. The resulting XML payload will now look like this:

Although the XML content looks a little bit different from dumping the content of the recordset directly to XML as shown in the earlier example, it is optimized for object serialization. This is the type of content that you could push into application queues or share with external applications.

Raising an Event in a Script Task

All existing SSIS Tasks and Components raise events that can be captured and displayed by the Execution Results tab by default. Optionally, these events can also be captured and logged into SSIS logging or event handlers. Event handlers are Control Flows that you set up and define to respond to specific events. They are literally Control Flow workflows within a package, and they enable you to customize the diagnostic information that the packages can provide at runtime.

If you have done any Windows GUI programming, you are familiar with events. An event is simply a message sent from some object saying that something just happened or is about to happen. To raise or fire an event within a Script Task, you use the Events property of the Dts object. More information about events can be found in Error and Event Handling Topic. The Events property on the Dts object is really an instance of the IDTSComponentEvents interface. This interface specifies seven methods for firing events:

• FireBreakpointHit: Supports the SQL Server infrastructure and is not intended to be used directly in code.
• FireError: Fires an event when an error occurs.
• FireInformation: Fires an event with information. You can fire this event when you want a set of information to be logged, possibly for auditing later.
• FireProgress: Fires an event when a certain progress level has been met.
• FireQueryCancel: Fires an event to determine if package execution should stop.
• FireWarning: Fires an event that is less serious than an error but more than just information.
• FireCustomEvent: Fires a custom-defined event.

In SSIS, any events you fire are written to all enabled log handlers that are set to log that event. Logging enables you to check what happened with your script when you’re not there to watch it run. Using events is a best practice for troubleshooting and auditing purposes, as you’ll see in the following example.

Example: Raising Some Events

The default way to view events while designing your package is to use the Execution Results tab at the top of your package in the SQL Server Data Tools design environment. To fire off some sample events and view them in this Execution Results tab, create a new package with a Script Task and add the System variable System::TaskName as a read-only variable. Then add the following code to the Main() function

This code will perform a simple loop operation that demonstrates firing the information, progress, warning, and error events. If you run the package, you can view the information embedded in these fire event statements in the final tab, either named Execution Results or Progress, depending on whether the designer is in Debug mode or not. These events are shown in below screen shot. Note that raising the error event results in the Script Task’s failure. You can comment out the FireError event to see the task complete successfully.

All the statements prefixed with the string [Script Task] were generated using these events fired from the Script Task. You can comment out the Dts.Events.FireError method calls to demonstrate to yourself that the task can complete successfully for warnings and informational events. Note that with the firing of an error, you can also force the task to generate a custom error with an error code and description. In fact, each of the events has a placeholder as the first parameter to store a custom information code. Continue to the next example to see how you can create an error handler to respond to the warning events that are fired from this Script Task.

Example: Responding to an Event

If you have already created a package for the preceding example, navigate to the Event Handlers tab. Event handlers are separate Control Flows that can be executed in response to an event. In the Raising Some Events example, you generated two warning events. One had an information code of one (1) and the other had the value of two (2). In this example, you are going to add an event handler to respond to those warning events and add some logic to respond to the event if the information code is equal to one (1). Select the Script Task executable and then select the OnWarning event handler. Click the hot link that states the following:

Click here to create an ‘OnWarning’ event handler for executable
‘Script Task’

This will create a Control Flow surface onto which you can drop SSIS Control Tasks that will execute if an OnWarning event is thrown from the Script Task you added to the package earlier. Drop a new Script Task into the Event Handler Control Flow surface and name it OnWarning Script Task. Your designer should look like shown in below screenshot.

To retrieve the information code sent in the Dts.Events.FireWarning method call, add two system-level variables, System::ErrorCode and System::ErrorDescription, to the Read-Only Variables collection of the OnWarning Script Task. These variables will contain the values of the InformationCode and Description parameters in the Dts.Events() methods. You can then retrieve and evaluate these values when an event is raised by adding the following code

The code checks the value of the first parameter, which is the value of the System::ErrorCode and the value raised in the Dts.Events.FireWarning method. If the value is equivalent to one (1), an action is taken to show a message box. This action could just as well be logging an entry to a database or sending an e-mail. If you rerun the package now, you’ll see that the first FireWarning event will be handled in your event handler and generate a message box warning. The second FireWarning event will also be captured by the event handler, but no response is made.

The event handler counter in the Progress or Execution Results tab is incremented to two (2). Raising events in the Script Tasks is a great way to get good diagnostic information without resorting to message boxes in your packages. See Error and Event Handling Topic for more details about handling errors and events in SSIS.

Example: Logging Event Information

Scripts can also be used to fire custom event information, which can then be logged as described previously. To configure the previous example events SSIS package to log event information, go to SSIS ⇒ Logging in the SQL Server Data Tools application. The Configure SSIS Logs dialog will appear. Select “SSIS log provider for XML files” in the Provider Type dropdown and click Add. Click the Configuration column and then select from the list to create an XML File Editor. For Usage type, select Create File and specify a path to a filename similar to 

NOTE In a production package you would set this value using an expression or parameter during runtime.

Click OK to close the File Connection Manager Editor dialog box. Your screen should look something like below the screen shot.

Now click the Package Node to start selecting what tasks in the package should log to the new provider, and check the box next to the provider name so that the log will be used. In the Details tab, select the specific OnWarning events to log. You can choose to log any of the available event types to the providers by also selecting them in the Details tab. Now your provider configuration should look like below the screen shot.

You can also use the Advanced tab for each selected event to control exactly what properties of the event are logged as well. If you run the package again, the file specified in the logging.

You’ll have other events in the file too, such as Package Start and Package End, but the preceding code snippet focuses on the event that your code fired.

This record contains basic information about the event, including the message, event execution time, and the computer and user that raised the event.

Using the Script Task to raise an event is just one way to get more diagnostic information into your SSIS log files. Read on to get a brief look at generating simple log entries.

Writing a Log Entry in a Script Task

Within a Script Task, the Log method of the Dts object writes a message to all enabled log providers. The Log method is simple and has three arguments:

• messageText: The message to log
• dataCode: A field for logging a message code
• dataBytes: A field for logging binary data

The Log method is similar to the FireInformation method of the Events property, but it is easier to use and more efficient — and you don’t need to create a specialized event handler to respond to the method call. All you need to do is set up a log provider within the package. In the previous section, you learned how to add a log provider to a package. The code in the next section logs a simple message with some binary data to all available log providers. This is quite useful for troubleshooting and auditing purposes. You can write out information at important steps in your script and even print out variable values to help you track down a problem.

Example: Scripting a Log Entry

This example demonstrates how to script a log entry by adding a few lines of code to the package in the previous examples that you used to raise events. First, add the following lines to the appropriate Script Task that matches the language you chose in the previous example 

Next, select the events for the ScriptTaskLogEntry event in the Details tab of the logging configuration. This tells the SSIS package logger to handle any custom logging instructions such as the one you just coded. Then run the package. You’ll see a set of additional logging instructions that look like this:

As you can see, the Script Task is highly flexible with the inclusion of the .NET-based VSTA capabilities. As far as controlling package flow or one-off activities, the Script Task is clearly very important. However, the Script Task doesn’t do all things well. If you want to apply programmatic logic to the Data Flow in an SSIS package, then you need to add to your knowledge of scripting in SSIS with the Script Component.

List of Related Microsoft Certification Courses: