Script Task
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. You use the Script task for work that must be done once in a package (or once per enumerated object), rather than once per data row. If you need 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.
The code created by the Script task 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.
- 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.
You can configure the Script task in the following ways:
- Provide the custom script that the task runs.
- Specify the name of the function that is the entry point in the script.
- Indicate whether the script is precompiled.
- Optionally, provide lists of read-only and read/write variables for use in the script.
The Script task uses Visual Studio for Applications (VSA) as its engine for writing and running scripts. To run a script, you must have VSA installed on the computers where the package runs.
For more information about writing code for the Script task, see Extending the Package with the Script Task.
If a script is used by more than one package, consider writing a custom task instead of using the Script task. For more information, see Developing a Custom Task.
Visual Studio for Applications Environment
The Script task provides access to the VSA environment. All standard features of the Visual Studio .NET environment are available, such as the color-coded Visual Studio editor, IntelliSense, and Object Explorer.
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.
The Script task offers several advantages over the ActiveX Script task:
- An integrated development environment. VSA offers you the same IDE that is provided by other Microsoft development tools.
- An integrated debugger. VSA makes it possible to debug scripts using the same debugger that is provided by other Microsoft development tools. Breakpoints in the script work seamlessly with breakpoints on Integration Services tasks and containers.
- The use of the common language runtime language.
- Integration with SSIS Designer.
The Script task replaces the ActiveX Script task. Use the ActiveX Script task only when necessary for backward compatibility. This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.
Optimization
VSA scripts must be compiled before the package runs. The Script task includes the option to precompile script into binary code when the package is saved. When script is precompiled, the language engine is not loaded at run time and the package runs more quickly; however, compiled binary files consume significant disk space. If you do not use the precompile option, the script is compiled at run time, which slows package execution but consumes less disk space. If storing large packages is not a problem, you should precompile your VSA scripts. Also, only precompiled script can be run in a 64-bit environment.
Another consideration when choosing whether to precompile script is that compiled script cannot be debugged. By default, the Script task is configured to precompile its script and its PreCompile property 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.
Custom Logging Messages Available on the Script Task
The following table describes the custom log entry for the Script task. For more information, see Implementing Logging in Packages and Custom Messages for Logging.
| Log entry | Description |
|---|---|
ScriptTaskLogEntry |
Reports the results of implementing logging in the script. A log entry is written for each call to the Log method of the Dts object. The entry is written when the code is run. For more information, see Logging in the Script Task. |
Configuring the Script Task
You can set properties through SSIS Designer or programmatically.
For more information about the properties that you can set in SSIS Designer, 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 Task Programmatically
For more information about programmatically setting these properties, click the following topic:
See Also
Concepts
Integration Services Tasks
Creating Package Control Flow
Integration Services Considerations on 64-bit Computers
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 July 2006 |
|
5 December 2005 |
|