Scripting (Integration Services)

Integration Services provides various features that let you perform administrative tasks programmatically, from inside a package or from an external scripting environment. Integration Services also lets you extend existing packages with custom logic by using the Script task and the Script component.

Programming Administrative Tasks in Integration Services

To perform administrative tasks programmatically, you have the following options:

  • Use the Integration Services object model to manage Integration Services packages, folders, and roles.

  • Use the Integration Services command prompt utilities, dtexec and dtutil, to run and manage packages.

  • Use an appropriate task in a package to run a program or batch file.

Managing Integration Services Objects with the Object Model

You can manage Integration Services packages, folders, and roles by writing managed code that uses the Integration Services object model, especially the properties and methods of the Application class. In addition to running packages, you can use the object model to perform the following management tasks:

For more information about how to use the Integration Services object model to run packages and to manage Integration Services objects, see Running and Managing Packages Programmatically.

Managing Integration Services Objects with Command Prompt Utilities

Integration Services provides two command prompt utilities: the dtexec utility (dtexec.exe) and the dtutil utility (dtutil.exe). You have the option of running these utilities directly at the command prompt, from a script, or from a batch file. Use these command prompt utilities to perform the following management tasks:

  • To run an Integration Services package, use the dtexec utility.

  • To move, copy, and delete Integration Services packages, use the dtutil utility.

For general information about the Integration Services command prompt utilities, see Command Prompt Utilities (Integration Services).

Running Commands with Integration Services Tasks

Integration Services has certain tasks that let you perform management tasks within an Integration Services package. These tasks let you run a Transact-SQL script, program, or batch file:

For information about other tasks that are available for use in Integration Services packages, see Integration Services Tasks.

Extending Integration Services Packages with Scripting

If you find that the built-in components for Integration Services do not meet your requirements, you can extend the power of Integration Services by coding your own extensions. The Script task and the Script component let you extend both the control flow and the data flow of an Integration Services package with very little coding. Both the Script task and the Script component use the Microsoft Visual Studio Tools for Applications (VSTA) development environment. VSTA supports both the Microsoft Visual Basic 2008 and Microsoft Visual C# 2008 programming languages. Also, by using VSTA, the Script task and the Script component benefit from all the functionality offered by the Microsoft .NET Framework class library and custom assemblies.

You can use scripting to extend an Integration Services package in the following ways:

  • To extend the control flow with custom logic, use the Script task.

  • To extend the data flow with custom sources, transformations, and destinations, see the Script component.

For more information, see Extending Packages with Scripting.

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.