| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Integration Services Programming Overview |
Integration Services Programming Overview |
chugugrace |
chugu |
maghan |
07/13/2023 |
sql |
integration-services |
reference |
|
[!INCLUDEsqlserver-ssis]
[!INCLUDEssNoVersion] [!INCLUDEssISnoversion] 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 [!INCLUDEssISnoversion]. 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 [!INCLUDEssISnoversion], you'll be programming against these two engines.
The following image depicts the architecture of [!INCLUDEssISnoversion].
:::image type="content" source="../integration-services/media/mw-dts-01.gif" alt-text="Integration Services architecture.":::
The [!INCLUDEssISnoversion] 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 [!INCLUDEssISnoversion] run-time engine lets developers automate the creation, configuration, and execution of packages and create custom tasks and other extensions.
For more information, see Extending the Package with the Script Task, Developing a Custom Task, and Building Packages Programmatically.
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.
[!INCLUDEssISnoversion] fully supports the [!INCLUDEmsCoName] [!INCLUDEdnprdnshort]. This lets developers program [!INCLUDEssISnoversion] in their choice of .NET-compliant languages. Although both the run-time engine and the data flow engine are written in native code, they're both available through a fully managed object model.
You can program [!INCLUDEssISnoversion] packages, custom tasks, and components in [!INCLUDEmsCoName] [!INCLUDEvsprvs] or in another code or text editor. [!INCLUDEvsprvs] offers the developer many tools and features to simplify and accelerate the iterative cycles of coding, debugging, and testing. [!INCLUDEvsprvs] also makes deployment easier. However, you don't need [!INCLUDEvsprvs] to compile and build [!INCLUDEssISnoversion] code projects. The [!INCLUDEdnprdnshort] SDK includes the [!INCLUDEvisual-basic] and [!INCLUDEc-sharp] compilers and related tools.
Important
By default, the [!INCLUDEdnprdnshort] is installed with [!INCLUDEssNoVersion], but the [!INCLUDEdnprdnshort] 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 [!INCLUDEdnprdnshort] 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 [!INCLUDEssISnoversion] Script task and Script component use [!INCLUDEmsCoName] [!INCLUDEvsprvs] Tools for Applications (VSTA) as an embedded scripting environment. VSTA supports [!INCLUDEmsCoName] Visual Basic and [!INCLUDEmsCoName] Visual C#.
Note
The [!INCLUDEssISnoversion] application programming interfaces are incompatible with COM-based scripting languages such as VBScript.
In [!INCLUDEssSQL19], the [!INCLUDEssISnoversion] assemblies were upgraded to .NET 4.0. There's a separate global assembly cache for .NET 4, located in *\<drive>*:\Windows\Microsoft.NET\assembly. You can find all of the [!INCLUDEssISnoversion] assemblies under this path, usually in the GAC_MSIL folder.
As in previous versions of [!INCLUDEssNoVersion], the core [!INCLUDEssISnoversion] extensibility .dll files are also located at *\<drive>*:\Program Files\Microsoft SQL Server\\\<nnn>\SDK\Assemblies. <nnn> identifies the version of SQL Server shared Files location.
The following table lists the assemblies that are frequently used when programming [!INCLUDEssISnoversion] using the [!INCLUDEdnprdnshort].
| 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. |