Script Task Vs 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 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 Extending the Data Flow with Custom Components.

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.

The Script task provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server 2005 Integration Services provides. The Script task can also combine functions in one script instead of using multiple tasks and transformations. The code is custom Microsoft Visual Basic .NET code that is compiled and executed at package run time.

You can use the Script task for the following purposes:

• Access data using other technologies that are not supported by built-in connection types. For example, a script can use Active Directory Service Interfaces (ADSI) to access and extract user names from Active Directory.
• Create a package-specific performance counter. For example, a script can create a performance counter that is updated while a complex or poorly performing task runs.
• Replace data in the source with different data in the destination. For example, a script can replace a two-digit state code in the source data with the state name in the destination data.
• Validate important columns in the source data and skip records that contain invalid data to prevent them from being copied to the destination. For example, a script can assess the reasonableness of a postage amount and skip records with extremely high or low amounts