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.
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 2008 or Microsoft Visual C# 2008. 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.
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.
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 2008 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# 2008 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:
' 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
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.
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.
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.
Provides access to connection managers defined in the package.
Provides an events interface to let the Script task raise errors, warnings, and informational messages.
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.
Logs information such as task progress and results to enabled log providers.
Reports the success or failure of the task.
Provides the transaction, if any, within which the task's container is running.
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.
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.
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 Business Intelligence Development Studio. 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.
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.
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.
When you debug a package that contains multiple Script tasks, the debugger hits breakpoints in only one Script task and will ignore breakpoints in the other Script tasks. If a Script task is part of a Foreach Loop or For Loop container, the debugger ignores breakpoints in the Script task after the first iteration of the loop.