Coding and Debugging the Script Task

Article
03/06/2017

After configuring the Script task in the Script Task Editor, you write your custom code in the Script task development environment.

Script Task Development Environment

The Script task uses Microsoft Visual Studio Tools for Applications (VSTA) as the development environment for the script itself.

Script code is written in Microsoft Visual Basic or Microsoft Visual C#. You specify the script language by setting the ScriptLanguage property in the Script Task Editor. If you prefer to use another programming language, you can develop a custom assembly in your language of choice and call its functionality from the code in the Script task.

The script that you create in the Script task is stored in the package definition. There is no separate script file. Therefore, the use of the Script task does not affect package deployment.

Note

When you design the package and debug the script, the script code is temporarily written to a project file. Because storing sensitive information in a file is a potential security risk, we recommend that you do not include sensitive information such as passwords in the script code.

By default, Option Strict is disabled in the IDE.

Script Task Project Structure

When you create or modify the script that is contained in a Script task, VSTA opens an empty new project or reopens the existing project. The creation of this VSTA project does not affect the deployment of the package, because the project is saved inside the package file; the Script task does not create additional files.

Project Items and Classes in the Script Task Project

By default, the Script task project displayed in the VSTA Project Explorer window contains a single item, ScriptMain. The ScriptMain item, in turn, contains a single class, also named ScriptMain. The code elements in the class vary depending on the programming language that you selected for the Script task:

When the Script task is configured for the Visual Basic 2010 programming language, the ScriptMain class has a public subroutine, Main. The ScriptMain.Main subroutine is the method that the runtime calls when you run your Script task.

By default, the only code in the Main subroutine of a new script is the line Dts.TaskResult = ScriptResults.Success. This line informs the runtime that the task was successful in its operation. The Dts.TaskResult property is discussed in Returning Results from the Script Task.
When the Script task is configured for the Visual C# programming language, the ScriptMain class has a public method, Main. The method is called when the Script task runs.

By default, the Main method includes the line Dts.TaskResult = (int)ScriptResults.Success. This line informs the runtime that the task was successful in its operation.

The ScriptMain item can contain classes other than the ScriptMain class. Classes are available only to the Script task in which they reside.

By default, the ScriptMain project item contains the following autogenerated code. The code template also provides an overview of the Script task, and additional information on how to retrieve and manipulate SSIS objects, such as variables, events, and connections.

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime.VSTAProxy

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
Partial Class ScriptMain

Private Sub ScriptMain_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

End Sub

Private Sub ScriptMain_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown
Try
' Unlock variables from the read-only and read-write variable collection properties
If (Dts.Variables.Count <> 0) Then
Dts.Variables.Unlock()
End If
Catch ex As Exception
        End Try
End Sub

Enum ScriptResults
Success = DTSExecResult.Success
Failure = DTSExecResult.Failure
End Enum

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts property. Connections, variables, events,
' and logging features are available as members of the Dts property as shown in the following examples.
'
' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
'
' To use the connections collection use something like the following:
' ConnectionManager cm = Dts.Connections.Add("OLEDB")
' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
'
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
' 
' To open Help, press F1.

Public Sub Main()
'
' Add your code here
'
Dts.TaskResult = ScriptResults.Success
End Sub

End Class

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime.VSTAProxy;
using System.Windows.Forms;

namespace ST_1bcfdbad36d94f8ba9f23a10375abe53.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain
    {
        private void ScriptMain_Startup(object sender, EventArgs e)
        {

        }

        private void ScriptMain_Shutdown(object sender, EventArgs e)
        {
            try
            {
                // Unlock variables from the read-only and read-write variable collection properties
                if (Dts.Variables.Count != 0)
                {
                    Dts.Variables.Unlock();
                }
            }
            catch
            {
            }
        }

        #region VSTA generated code
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ScriptMain_Startup);
            this.Shutdown += new System.EventHandler(ScriptMain_Shutdown);
        }
        enum ScriptResults
        {
            Success = DTSExecResult.Success,
            Failure = DTSExecResult.Failure
        };

        #endregion

        /*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/

        public void Main()
        {
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }

Additional Project Items in the Script Task Project

The Script task project can include items other than the default ScriptMain item. You can add classes, modules, and code files to the project. You can also use folders to organize groups of items. All the items that you add are persisted inside the package.

References in the Script Task Project

You can add references to managed assemblies by right-clicking the Script task project in Project Explorer, and then clicking Add Reference. For more information, see Referencing Other Assemblies in Scripting Solutions.

Note

You can view project references in the VSTA IDE in Class View or in Project Explorer. You open either of these windows from the View menu. You can add a new reference from the Project menu, from Project Explorer, or from Class View.

Interacting with the Package in the Script Task

The Script task uses the global Dts object, which is an instance of the ScriptObjectModel class, and its members to interact with the containing package and with the Integration Services runtime.

The following table lists the principal public members of the ScriptObjectModel class, which is exposed to Script task code through the global Dts object. The topics in this section discuss the use of these members in more detail.

Member	Purpose
Connections	Provides access to connection managers defined in the package.
Events	Provides an events interface to let the Script task raise errors, warnings, and informational messages.
ExecutionValue	Provides a simple way to return a single object to the runtime (in addition to the `TaskResult`) that can also be used for workflow branching.
Log	Logs information such as task progress and results to enabled log providers.
TaskResult	Reports the success or failure of the task.
Transaction	Provides the transaction, if any, within which the task's container is running.
Variables	Provides access to the variables listed in the `ReadOnlyVariables` and `ReadWriteVariables` task properties for use within the script.

The ScriptObjectModel class also contains some public members that you will probably not use.

Member	Description
VariableDispenser	The Variables property provides more convenient access to variables. Although you can use the VariableDispenser, you must explicitly call methods to lock and unlock variables for reading and writing. The Script task handles locking semantics for you when you use the Variables property.

Debugging the Script Task

To debug the code in your Script task, set at least one breakpoint in the code, and then close the VSTA IDE to run the package in SQL Server Data Tools (SSDT). When package execution enters the Script task, the VSTA IDE reopens and displays your code in read-only mode. After execution reaches your breakpoint, you can examine variable values and step through the remaining code.

Warning

You can debug the Script task when you run the package in 64-bit mode.

Note

You must execute the package to debug into your Script task. If you execute only the individual task, breakpoints in the Script task code are ignored.

Note

You cannot debug a Script task when you run the Script task as part of a child package that is run from an Execute Package task. Breakpoints that you set in the Script task in the child package are disregarded in these circumstances. You can debug the child package normally by running it separately.

Note

When you debug a package that contains multiple Script tasks, the debugger debugs one Script task. The system can debug another Script task if the debugger completes, as in the case of a Foreach Loop or For Loop container.

External Resources

Blog entry, VSTA setup and configuration troubles for SSIS 2008 and R2 installations, on blogs.msdn.com.

Integration Services icon (small) Stay Up to Date with Integration Services
For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:

Visit the Integration Services page on MSDN

For automatic notification of these updates, subscribe to the RSS feeds available on the page.

Share via

Coding and Debugging the Script Task

Script Task Development Environment

Script Task Project Structure

Project Items and Classes in the Script Task Project

Additional Project Items in the Script Task Project

References in the Script Task Project

Interacting with the Package in the Script Task

Debugging the Script Task

External Resources

See Also

Additional resources