Jaa

Coding and Debugging the Script Component

  • Artikkeli

In SSIS Designer, the Script component has two modes: metadata design mode and code design mode. When you open the Script Transformation Editor, the component enters metadata design mode, in which you configure metadata and set component properties. After you have set the properties of the Script component and configured the input and outputs in metadata design mode, you can switch to code design mode to write your custom script. For more information about metadata design mode and code design mode, see Configuring the Script Component in the Script Component Editor.

Important

For important information about referencing other assemblies and namespaces from a Script task project, see Referencing Other Assemblies in Scripting Solutions.

Writing the Script in Code Design Mode

Script Component Development Environment

Click Design Script on the Script page of the Script Transformation Editor to open the Visual Studio for Applications (VSA) IDE, where you can write your script. The VSA IDE includes all the standard features of the Visual Studio .NET environment, such as the color-coded Visual Studio editor, IntelliSense, and Object Browser.

The scripting language is Microsoft Visual Basic .NET. 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 component.

The script that you create in the Script component 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 component does not affect package deployment.

By default, Option Strict is enabled in the IDE.

Script Component Project Structure

The power of the Script component is its ability to generate infrastructure code that reduces the amount of code that you must write. This feature relies on the fact that inputs and outputs and their columns and properties are fixed and known in advance. Therefore, any subsequent changes that you make to the component's metadata may invalidate the code that you have written, causing compilation errors during execution of the package.

Project Items and Classes in the Script Component Project

When you switch to code design mode, the VSA IDE opens and displays the ScriptMain project item. The ScriptMain project item contains the editable ScriptMain class, which serves as the entry point for the script and which is where you write your code. The script project contains two additional auto-generated read-only project items:

  • The ComponentWrapper project item contains three classes:
    • The UserComponent class, which inherits from ScriptComponent and contains the methods and properties that you will use to process data and to interact with the package. The ScriptMain class inherits from the UserComponent class.
    • A Connections collection class that contains references to the connections selected on the Connection Manager page of the Script Transformation Editor.
    • A Variables collection class that contains references to the variables entered in the ReadOnlyVariable and ReadWriteVariables properties on the Script page of the Script Transformation Editor.
  • The BufferWrapper project item contains a class that inherits from ScriptBuffer for each input and output configured on the Inputs and Outputs page of the Script Transformation Editor. Each of these classes contains typed accessor properties that correspond to the configured input and output columns, and the data flow buffers that contain the columns.

For information about how to use these objects, methods, and properties, see Understanding the Script Component Object Model. For information about how to use the methods and properties of these classes in a particular type of Script component, see the section Additional Script Component Examples. The example topics also contain complete code samples.

By default, the ScriptMain project item contains the following autogenerated code:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET.
' ScriptMain is the entry point class for script components.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()
        '
        ' Add rows by calling the AddRow method on the member variable called "<Output Name>Buffer"
        ' For example, if your output was named "My Output", call MyOutputBuffer.AddRow().
        '
    End Sub

End Class

Additional Project Items in the Script Component Project

The Script component project can include items other than the default ScriptMain item. You can add classes, modules, code files, and folders 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 component project by importing it.

References in the Script Component Project

You can add references to managed assemblies by right-clicking the References folder in the Project Explorer within the Script component 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 on 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. Therefore, a copy of any assembly that you want to reference must 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 Component

The custom script that you write in the Script component can access and use variables and connection managers from the containing package through strongly-typed accessors in the auto-generated base classes. However, you must configure both variables and connection managers before entering code-design mode if you want to make them available to your script. You can also raise events and perform logging from your Script component code.

The autogenerated project items in the Script component project provide the following objects, methods, and properties for interacting with the package.

Package Feature Access Method

Variables

Use the named and typed accessor properties in the Variables collection class in the ComponentWrapper project item, exposed through the Variables property of the ScriptMain class.

Connections

Use the named and typed accessor properties in the Connections collection class in the ComponentWrapper project item, exposed through the Connections property of the ScriptMain class.

Events

Raise events by using the ComponentMetaData property of the ScriptMain class and the Fire<X> methods of the IDTSComponentMetaData90 interface.

Logging

Perform logging by using the Log method of the ScriptMain class.

Debugging the Script Component

The Script component does not support the use of breakpoints. Therefore, you cannot step through your code and examine values as the package runs. You can monitor the execution of the Script component by using the following methods:

  • Interrupt execution and display a modal message by using the MessageBox.Show method in the System.Windows.Forms namespace. (Remove this code after debugging is complete.)
  • Raise events for informational messages, warnings, and errors. The FireInformation, FireWarning, and FireError methods display the event description in the Visual Studio Output window; however, the FireProgress method, the Console.Write method, and Console.WriteLine method do not display any information in the Output window. Messages from the FireProgress event appear on the Progress tab of SSIS Designer. For more information, see Raising Events in the Script Component.
  • Log events or user-defined messages to enabled logging providers. For more information, see Logging in the Script Component.

If you just want to examine the output of a Script component configured as a source or as a transformation, without saving the data to a destination, you can terminate the data flow with a Row Count Transformation and attach a data viewer to the output of the Script component. For information about data viewers, see Debugging Data Flow.

In This Section

For more information about coding the Script component, see the following topics in this section.

Topic Description

Understanding the Script Component Object Model

Explains how to use the objects, methods, and properties available in the Script component.

Referencing Other Assemblies in Scripting Solutions

Explains how to reference objects from the .NET Framework class library in the Script component.

Simulating an Error Output for the Script Component

Explains how to simulate an error output for rows that raise errors during processing by the Script component.

See Also

Reference

Referencing Other Assemblies in Scripting Solutions

Concepts

Configuring the Script Component in the Script Component Editor
Understanding the Script Component Object Model

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Noted that custom assemblies can be used to program in other programming languages.
  • Noted that scripts are saved automatically.
  • Noted that Option Strict is on by default.
  • Noted that code files can be exported and imported.

5 December 2005
New content:
  • Added the default code that is autogenerated in a new Script component.
  • Added tip about how to use the Row Count transformation and data viewers for debugging.