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.
Important
For important information about referencing other assemblies and namespaces from a Script task project, see Referencing Other Assemblies in Scripting Solutions.
Script Task Development Environment
The Script task uses Microsoft Visual Studio for Applications (VSA) as the development environment for the script itself. Script code is written in Visual Basic .NET within the powerful Visual Studio IDE. 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 automatically saved each time you close the IDE, and 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 enabled in the IDE.
Script Task Project Structure
When you create or modify the script contained in a Script task, VSA opens an empty new project or reopens the existing project. The creation of this VSA 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 VSA Project Explorer window contains a single item, ScriptMain. The ScriptMain item, in turn, contains a single class, also named ScriptMain. This 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 = Dts.Results.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.
The ScriptMain item can contain classes other than the ScriptMain class. If you remove the ScriptMain class, you must change the EntryPoint property of the Script task to specify the class that is executed when the task is run. 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
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dts.TaskResult = Dts.Results.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, and you can use folders to organize groups of items.
All the items that you add are persisted inside the package. However you can save a copy of an item to the file system by right-clicking the item in the VSA Project Explorer window and selecting Export. You can also add an existing Visual Basic .NET code file to the Script task project by importing it.
References in the Script Task Project
You can add references to managed assemblies by right-clicking the References folder in the Project Explorer within the Script task project, and then clicking Add Reference. VSA supports references only to managed assemblies. For more information, see Referencing Other Assemblies in Scripting Solutions.
Note
You can view project references in the VSA IDE in the Class View window or in the Project Explorer. Select Project Explorer from the View menu to open the Project Explorer window. You can add a new reference from the Project menu or from the Project Explorer, but not from the Class View window. The Project menu is available while you work in a code window or in the Project Explorer, but not when you select an item in the Class View window.
Note
The .NET tab of the Add Reference dialog box in Microsoft Visual Studio for Applications lists the managed assemblies found in the %windir%\Microsoft.NET\Framework\v2.0.xxxxx folder. Therefore, by default, this list is largely limited to assemblies from the Microsoft .NET Framework class library. The contents of the list are determined exclusively by file location and not by installation in the global assembly cache (GAC) or by other assembly attributes or properties. As a result, a copy of any assembly that you want to reference needs to be present in the specified folder. The Add Reference dialog box in VSA does not include the Browse button that is present in Microsoft Visual Studio for locating and referencing managed assemblies in other folders, and does not include the COM tab for referencing COM components.
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 |
|---|---|
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.
| Member | Description |
|---|---|
Obsolete. The Log method is a simpler and more direct method to accomplish logging. |
|
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 VSA IDE to run the package in Business Intelligence Development Studio. When package execution enters the Script task, the VSA 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.
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 within the Script task in the child package are disregarded in these circumstances. You can debug the child package normally by running it separately.
See Also
Reference
Referencing Other Assemblies in Scripting Solutions
Configuring the Script Task in the Script Task Editor
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
14 April 2006 |
|