The Script task provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server Integration Services provides. The Script task can also combine functions in one script instead of using multiple tasks and transformations. You use the Script task for work that must be done once in a package (or once per enumerated object), instead than once per data row.
You can use the Script task for the following purposes:
- Access data by 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.
- Identify whether specified files are empty or how many rows they contain, and then based on that information affect the control flow in a package. For example, if a file contains zero rows, the value of a variable set to 0, and a precedence constraint that evaluates the value prevents a File System task from copying the file.
If you have to use the script to do the same work for each row of data in a set, you should use the Script component instead of the Script task. For example, if you want to assess the reasonableness of a postage amount and skip data rows that have very high or low amounts, you would use a Script component. For more information, see Script Component.
If more than one package uses a script, consider writing a custom task instead of using the Script task. For more information, see Developing a Custom Task.
After you decide that the Script task is the appropriate choice for your package, you have to both develop the script that the task uses and configure the task itself.
Writing and Running the Script that the Task Uses
The Script task uses Microsoft Visual Studio Tools for Applications (VSTA) as the environment in which you write the scripts and the engine that runs those scripts.
VSTA provides all the standard features of the Visual Studio environment, such as the color-coded Visual Studio editor, IntelliSense, and Object Explorer. VSTA also uses the same debugger that other Microsoft development tools use. Breakpoints in the script work seamlessly with breakpoints on Integration Services tasks and containers. VSTA supports both the Microsoft Visual Basic and Microsoft Visual C# programming languages.
To run a script, you must have VSTA installed on the computer where the package runs. When the package runs, the task loads the script engine and runs the script. You can access external .NET assemblies in scripts by adding references to the assemblies in the project.