Upgrade your Internet Experience
Microsoft.com
Welcome Sign in
SQL Server Developer Center
Click to Rate and Give Feedback

  Switch on low bandwidth view
Community Content
In this section
Statistics Annotations (0)
Other versions are also available for the following:
SQL Server 2008 Books Online (June 2009)
Script Component

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 that use 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.

ms137640.note(en-us,SQL.100).gifNote:
If the Script component contains a script that tries to read the value of a column that is NULL, the Script component fails when you run the package. We recommend that your script use the IsNull method to determine whether the column is NULL before trying to read the column value.

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.

After you decide that the Script component is the appropriate choice for your package, you have to configure the inputs and outputs, develop the script that the component uses, and configure the component itself.

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.

The Script component uses Microsoft Visual Studio Tools for Applications (VSTA) as the environment in which you write the scripts. You access VSTA from the Script Transformation Editor. For more information, see Script Transformation Editor (Script Page).

The Script component provides a VSTA 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.

VSTA includes all the standard features of the Visual Studio 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. When you are designing the package, the script code is temporarily written to a project file.

VSTA supports the Microsoft Visual Basic 2008 and Microsoft Visual C# 2008 programming languages.

For information about how to program the Script component, see Extending the Data Flow with the Script Component. For more specific information about how to configure 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.

ms137640.note(en-us,SQL.100).gifNote:
Unlike earlier versions where you could indicate whether the scripts were precompiled, all scripts are precompiled in SQL Server 2008 Integration Services (SSIS). When a script is precompiled, the language engine is not loaded at run time and the package runs more quickly. However, precompiled binary files consume significant disk space.

You can configure the Script component in the following ways:

  • Select the input columns to reference.
    ms137640.note(en-us,SQL.100).gifNote:
    You can configure only one input when you use the SSIS Designer.

  • Provide the script that the component runs.
  • Specify the script language.
  • Provide comma-separated lists of read-only and read/write variables.
  • Add more outputs, and add output columns to which the script assigns.

You can set properties through SSIS Designer or programmatically.

Configuring the Script Component in the Designer

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 how to set these properties in SSIS Designer, click the following topic:

Configuring the Script Component Programmatically

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:

Integration Services icon (small)  Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.

Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
© 2009 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker