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.
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.
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.
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 Add or Remove Product Documentation for SQL Server.
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 and Microsoft Visual C#.
The Integration Services application programming interfaces are incompatible with COM-based scripting languages such as VBScript.
In SQL Server 2017, the Integration Services assemblies were upgraded to .NET 4.0. There is a separate global assembly cache for .NET 4, located in <drive>:\Windows\Microsoft.NET\assembly. You can find all of the Integration Services assemblies under this path, usually in the GAC_MSIL folder.
As in previous versions of SQL Server, the core Integration Services extensibility .dll files are also located at <drive>:\Program Files\Microsoft SQL Server\100\SDK\Assemblies.
Commonly Used Assemblies
The following table lists the assemblies that are frequently used when programming Integration Services using the .NET Framework.
|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.|