Coding and Debugging the Script Task

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.

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.

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.

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

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.

External Resources

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

See Also

Reference

Referencing Other Assemblies in Scripting Solutions

Configuring the Script Task in the Script Task Editor