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
Incomecolumn.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.
Note
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.
Understanding the 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.
Writing the Script that the Component Uses
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 C# and Microsoft Visual Basic 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.
Note
Unlike earlier versions where you could indicate whether the scripts were precompiled, all scripts are precompiled in SQL Server 2008 Integration Services (SSIS) and later versions. 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.
Configuring the Script Component
You can configure the Script component in the following ways:
Select the input columns to reference.
Note
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: