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.
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:
To get a list of packages stored in a particular location, see Enumerating Available Packages Programmatically.
To determine whether an individual package or folder exists, or to manage the folders in which packages are stored, see Managing Packages and Folders Programmatically.
To get a list of running packages and to stop a running package, see Managing Running Packages Programmatically.
To get a list of roles that is available on a server or assigned to a package, see Managing Package Roles Programmatically.
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:
To run a Transact-SQL script, use the Execute SQL task.
To run a program or batch file, use the Execute Process task.
For information about other tasks that are available for use in Integration Services packages, see Integration Services Tasks.
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.