Integration Services Programming

Microsoft SQL Server 2005 Integration Services (SSIS) includes a completely rewritten object model, which has been enhanced with many features that make extending and programming packages easier, more flexible, and more powerful. Developers can extend and program almost every aspect of Integration Services packages.

As an Integration Services developer, there are two fundamental approaches that you can take to Integration Services programming:

  • You can extend packages by writing components that become available within SSIS Designer to provide custom functionality in a package.
  • You can create, configure, and run packages programmatically from your own applications.

If you find that the built-in components in Integration Services do not meet your requirements, you can extend the power of Integration Services by coding your own extensions. In this approach, you have two discrete options:

  • For ad hoc use in a single package, you can create a custom task by writing code in the Script task, or a custom data flow component by writing code in the Script component, which you can configure as a source, transformation, or destination. These powerful wrappers write the infrastructure code for you and let you focus exclusively on developing your custom functionality; however, they are not easily reusable elsewhere.
  • For use in multiple packages, you can create custom Integration Services extensions such as connection managers, tasks, enumerators, log providers, and data flow components. The managed Integration Services object model contains base classes that provide a starting point and make developing custom extensions easier than ever.

If you want to create packages dynamically, or to manage and run Integration Services packages outside the development environment, you can manipulate packages programmatically. You can load, modify, and run existing packages, or you can create and run entirely new packages programmatically. In this approach, you have a continuous range of options:

  • Load and run an existing package without modification.
  • Load an existing package, reconfigure it (for example, specify a different data source), and run it.
  • Create a new package, add and configure components, making changes object by object and property by property, save it, and then run it.

These approaches to Integration Services programming are described in this section and demonstrated with examples.

In This Section

Term Definition

Integration Services Programming Architecture

Describes the roles of control flow and data flow in Integration Services development.

Understanding Synchronous and Asynchronous Transformations

Describes the important distinction between synchronous and asynchronous outputs and the components that use them in the data flow.

Extending Packages with Scripting

Describes how to extend the control flow by using the Script task, or the data flow by using the Script component.

Extending Packages with Custom Objects

Describes how to create and program custom tasks, data flow components, and other package objects for use in multiple packages.

Building Packages Programmatically

Describes how to create, configure, and save Integration Services packages programmatically.

Managing Packages Programmatically

Describes how to enumerate, run, and manage Integration Services packages programmatically.

Programming Samples

Lists and describes the Integration Services programming samples. Samples are available as an optional installation component.

Data Flow Properties Reference

Lists the custom properties of data flow components. These custom properties exist only at run time and are not included in the managed programming reference.

Integration Services Error and Message Reference

Lists the predefined Integration Services error codes, together with their symbolic names and descriptions.

See Also

Concepts

SQL Server Integration Services
Integration Services Developer InfoCenter

Other Resources

Integration Services Development Enhancements
SQL Server Programming Overview

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006

Changed content:
  • Added links to new package management topics.

5 December 2005

Changed content:
  • Added links to new programming topics.
  • Reorganized supporting content.
  • Added more links to related concepts.