Share via

Script Component

  • Article

The Script component hosts script and enables a package to include and run custom script code. You can use the Script component in packages for the following purposes:

  • Apply multiple transformations to data instead of using multiple transformations in the data flow. For example, a script can add the values in two columns and then calculate the average of the sum.
  • Access business rules in an existing .NET assembly. For example, a script can apply a business rule that specifies the range of values that are valid in an Income column.
  • Use custom formulas and functions in addition to the functions and operators that the Integration Services expression grammar provides. For example, validate credit card numbers using the LUHN formula.
  • Validate column data and skip records that contain invalid data. For example, a script can assess the reasonableness of a postage amount and skip records with extremely high or low amounts.

The Script component provides an easy and quick way to include custom functions in a data flow. However, if you plan to reuse the script code in multiple packages, you should consider programming a custom component instead of using the Script component. For more information, see Developing a Custom Data Flow Component.

The Script component can be used as a source, a transformation, or a destination. This component supports one input and multiple outputs. Depending on how the component is used, it supports either an input or outputs or both. The script is invoked by every row in the input or output.

  • If used as a source, the Script component supports multiple outputs.
  • If used as a transformation, the Script component supports one input and multiple outputs.
  • If used as a destination, the Script component supports one input.

The Script component does not support error outputs.

You can configure the Script component in the following ways:

  • Select input columns to reference.
  • Provide the script that the component runs.
  • Specify whether the script is precompiled.
  • Provide comma-separated lists of read-only and read/write variables.
  • Add more outputs, and add output columns to which the script assigns values.

Using the Script Transformation Editor dialog box in SSIS Designer you can configure only one input.

To write your scripts, access the Visual Studio for Applications (VSA) environment from the Script Transformation Editor dialog box. VSA includes all the standard features of the Visual Studio .NET environment, such as the color-coded Visual Studio editor, IntelliSense, and Object Browser. The script that the Script component uses is stored in the package definition. There is no separate script file.

Script Component Modes

In the SSIS Designer, the Script component has two modes: metadata-design mode and code-design mode. In metadata-design mode, you can add and modify the Script component inputs and outputs, but you cannot write code. After all the inputs and outputs are configured, you switch to code-design mode to write the script. The Script component automatically generates base code from the metadata of the inputs and outputs. If you change the metadata after the Script component generates the base code, your code may no longer compile because the updated base code may be incompatible with your code.

Script

The Script component provides a VSA project that includes an auto-generated class, named ScriptMain, that represents the component metadata. For example, if the Script component is used as a transformation that has three outputs, ScriptMain includes a method for each output. ScriptMain is the entry point to the script.

The Visual Studio for Applications development environment for scripts supports only Microsoft Visual Basic .NET as a scripting language.

For information about programming the Script component, see Extending the Data Flow with the Script Component. For more specific information about configuring the Script component as a source, transformation, or destination, see Developing Specific Types of Script Components. For additional examples such as an ODBC destination that demonstrate the use of the Script component, see Additional Script Component Examples.

Precompiled Script

You can configure the Script component to precompile the script. If the script is precompiled it will start more quickly and the script can run in a 64-bit environment. However, the package size is larger when it contains precompiled scripts. Moreover, compiled script cannot be debugged. You should consider these factors when you decide whether to precompile script.

By default, the Script component is configured to precompile its script and the PreCompile property of the component is set to True. The PreCompile property is typically set to False during the development and debugging of a package, and then reset to True before the package is deployed.

Configuring the Script Component

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set in the Script Transformation Editor dialog box, click one of the following topics:

For more information about the properties that you can set in the Properties window or programmatically, click one of the following topics:

For more information about how to set properties, click one of the following topics:

See Also

Concepts

Integration Services Transformations
Integration Services Considerations on 64-bit Computers

Other Resources

Extending the Data Flow with the Script Component

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Noted restrictions on use of scripting languages..

5 December 2005
Changed content:
  • Added description of PreCompile property.