Integration Services Programming Overview

SQL Server Integration Services has an architecture that separates data movement and transformation from package control flow and management. There are two distinct engines that define this architecture and that can be automated and extended when programming Integration Services. The run-time engine implements the control flow and package management infrastructure that lets developers control the flow of execution and set options for logging, event handlers, and variables. The data flow engine is a specialized, high performance engine that is exclusively dedicated to extracting, transforming, and loading data. When programming Integration Services, you will be programming against these two engines.

The following image depicts the architecture of Integration Services.

Integration Services Run-time Engine

The Integration Services run-time engine controls the management and execution of packages, by implementing the infrastructure that enables execution order, logging, variables, and event handling. Programming the Integration Services run-time engine lets developers automate the creation, configuration, and execution of packages and create custom tasks and other extensions.

For more information, see Using the Script Task to Extend a Package, Developing a Custom Task, and Building Packages Programmatically.

Integration Services Data Flow Engine

The data flow engine manages the data flow task, which is a specialized, high performance task dedicated to moving and transforming data from disparate sources. Unlike other tasks, the data flow task contains additional objects called data flow components, which can be sources, transformations, or destinations. These components are the core moving parts of the task. They define the movement and transformation of data. Programming the data flow engine lets developers automate the creation and configuration of the components in a data flow task, and create custom components.

For more information, see Extending the Data Flow with the Script Component, Developing a Custom Data Flow Component, and Building Packages Programmatically.

Supported Languages

Integration Services fully supports the Microsoft .NET Framework. This lets developers program Integration Services in their choice of .NET-compliant languages. Although both the run-time engine and the data flow engine are written in native code, they are both available through a fully managed object model.

You can program Integration Services packages, custom tasks, and components in Microsoft Visual Studio or in another code or text editor. Visual Studio offers the developer many tools and features to simplify and accelerate the iterative cycles of coding, debugging, and testing. Visual Studio also makes deployment easier. However, you do not need Visual Studio to compile and build Integration Services code projects. The .NET Framework SDK includes the Visual Basic and Visual C# compilers and related tools.

Important

By default, the .NET Framework is installed with SQL Server, but the .NET Framework SDK is not. Unless the SDK is installed on the computer and the SDK documentation is included in the Books Online collection, links to SDK content in this section will not work. After you have installed the .NET Framework SDK, you can add the SDK documentation to the Books Online collection and table of contents by following the instructions in Adding and Removing Help Collections.

The Integration Services Script task and Script component use Microsoft Visual Studio Tools for Applications (VSTA) as an embedded scripting environment. VSTA supports Microsoft Visual Basic 2008 and Microsoft Visual C# 2008.

Note

The Integration Services application programming interfaces are incompatible with COM-based scripting languages such as VBScript.

Commonly Used Assemblies

The following table lists the assemblies that are frequently used when programming Integration Services using the .NET Framework.

Assembly	Description
Microsoft.SqlServer.ManagedDTS.dll	Contains the managed run-time engine.
Microsoft.SqlServer.RuntimeWrapper.dll	Contains the primary interop assembly (PIA), or wrapper, for the native run-time engine.
Microsoft.SqlServer.PipelineHost.dll	Contains the managed data flow engine.
Microsoft.SqlServer.PipelineWrapper.dll	Contains the primary interop assembly (PIA), or wrapper, for the native data flow engine.

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: Visit the Integration Services page on MSDN Visit the Integration Services page on TechNet For automatic notification of these updates, subscribe to the RSS feeds available on the page.

See Also

Concepts

Planning and Architecture (Integration Services)