SSIS Architecture

         SSIS architecture is depicted in the below diagram (as given by Microsoft for SSIS 2008).

Architecture basically explains what are the components of SSIS, where SSIS is installed, what it is that keeps it running.

 

 

         SSIS Designer

SSIS Designer is a graphical tool that you can use to create and maintain Integration Services packages. In SQL Server 2012, SSIS Designer is available in SQL Services Data Tools.

 

SQL Server Import and Export Wizard

The SQL Server Import and Export Wizard can copy data to and from any data source for which a managed .NET Framework data provider or a native OLE DB provider is available. This wizard also offers the simplest method to create an Integration Services package that copies data from a source to a destination.

 

Object Model

SSIS object model includes managed APIs to create custom components for use in packages or custom applications that create, load, run, and manage packages.

NOTE: Custom applications or custom tasks or transformations can be created by using any common language runtime (CLR) compliant language.

 

Integration Services Runtime

The Integration Services runtime saves the layout of packages, runs packages, and provides support for logging, breakpoints, configuration, connections, and transactions.

 

Integration Services Service

The Integration Services service helps to

·         Monitor running IS packages

·         Manage the storage of packages. Packages can be stored in file system and also in msdb database.

 

As per the diagram, I guess that SSIS Managed Mode comprises of package creation using sophisticated SSIS Wizard, SSIS Designer and custom applications.

Whereas in the Native mode, command line utilities and basic custom applications are used to create the packages.

 

Package

A package is an object that defines the work flow and data flow the ETL logic.

The components of a package are: Tasks (Control Flow and Data Flow), Containers, Enumerators, Log providers, Event Handlers, Connection Managers.

 

 

Tasks and other executables

The Integration Services run-time executables are the package, containers, tasks, and event handlers that Integration Services includes. Run-time executables also include custom tasks.

 

In SSIS, Control flow tab is used to generate the work flow

 

Data flow task is used to migrate the data from source to destination (the actual Extract-Transform-Load). Data Flow Engine is used for this purpose.

 

Data Flow engine (also known as the pipeline) and Data Flow components

The Data Flow task encapsulates the data flow engine. The data flow engine:

·         Calls the sources that extract data from files and relational databases.

·         Provides the in-memory buffers that move data from source to destination.

·         Manages the transformations that modify data

·         Manages the destinations that load data or make data available to other processes.

 

Integration Services data flow components are the sources, transformations, and destinations that Integration Services includes. You can also include custom components in a data flow.

 

 

Comparison of Control flow and Data flow is given below:

	Control Flow	Data Flow
Orientation	Process Oriented	Data Oriented
Comprises	Tasks and Container	Source, Transformation and Destination
Connected by	Precedence constraint	Paths
Smallest unit	Task	Component
Outcome	Finite- Success, Failure, Completion	Not fixed

 

Enumerator - SSIS uses enumerators to loop over files in a folder, tables in a database, records in a row set and items in an objects.

 

Connection Manager - It acts like a bridge between a package object and physical data object. It provides logical representation of a connection at design time.

During the run-time, the IS service points to the physical connection that is described in the properties of a connection manager.

 

Event Handlers - On the Event handlers tab, workflows can be configured to respond to any package events, generally like failures, starting, warnings etc.

 

Log Providers - SSIS include many default providers to log the information of SSIS package. We have the following types of LOG providers in SSIS:

·         SSIS Log Provider for Text Files

·         SSIS Log Provider for SQL Profiler

·         SSIS Log Provider for SQL Server

·         SSIS Log Provider for Windows Event Log

·         SSIS Log Provider for XML Files