• Home
  • Blog
  • MSBI
  • SQL Server Integration Services – SSIS Architecture Overview

SQL Server Integration Services – SSIS Architecture Overview

SSIS (SQL Server Integration Services)

SSIS are 2 types
ETL Operations (Extracting Transform Load)
E- Extracting –> Getting data
T- Transform –> performing intermediate operations
L- Load –> Load to destination
DB <-> DB
DB <-> File
File <-> File

Administrative Tasks:

  • Taking backup of a data base
  • Sharing data base
  • History clean up
  • Transferring data base/ log/error msg etc…

Databases: Oracle, SQL server, Tara data etc..
Files: XML, Excel, Flat file. Raw file etc…

SSIS Architecture

There are 4 important components in SSIS ARCHITECTURE

  1. Object model
  2. SSIS runtime
  3. Integration services service
  4. Data flow task
FREE Demo Class From Industry Experts - Enroll Now.!! SSIS training

SSIS Designer: It’s a native tool to create ‘IS’ packages and it components.
Object model: It is an application programming interface which connects and understands custom tools and components.
SSIS runtime: This is ‘CLR’ which saves the layout of the packages (.dtx) runs the packages and manages the package components.
Integration services service: This component helps us to store the packages in SQL server_database (MSDB), managing the packages and running the packages.


To move the data between source to destination and perform different operations, data flow task is required.
It uses various “Inline buffers” while processing the data.
It uses a “data pipe line engine” to move the data from source to destination and to manager buffers.

MindMajix Youtube Channel

Package and its Components

Package is on important component in SSIS Architecture.

  • It can be constructed through custom tools (or) native tools.
  • It performs all operations such as “ETL” and administrative task.
  • It uses various other components as part of its processing. Ex: Logging, event handling, package configuration etc.
  • Control flow task is mandatory for every package.
  • To move the data from source to destination data flow task requires.

–> SQL engine (generates plan to execute package)
–> Data pipe line engine (only inside data flow task)

Frequently Asked SSIS Interview Questions & Answers

Different between DTS and SSIS

SQL server 7.0 introduced available 2000 on words.SQL server 2005 on words available.
Designed for ETS [Extract Transform Sources].Designed for ETL [Extract Transform Load].
It consists of single pane (i.e., pane means screen or frame) for all operation. It has data transformations work flow etc.It consists of multiple pane for multiple operations. It has
control flow,
data flow,
package explorer,
event handling.
Data transformations available.Data flow task introduced and transformations embedded.
No DSV [Data Source View] No connection manage, No event handling, No looping through folders, files.Available [introduced].
Message boxes displayed in active –X script.Message boxes displayed in script task.
Less transformation.More transformation.
Partial BI support (less)Full support to BI.
No deployment wizardDeployment wizards are there.
Saved inSaved in local file system. Deployed to SQL server.
a) Enterprise manager (SQL server) 
b) File system (structured storage file )

2008 to 2008R2

  • 2008 R2 is the “second release” of 2008.
  • Code name “KILIMANJARO”.
  • Released in middle of 2009.

Supported Features
–> Max 25 instances in CMS.
–> Max 256 logical processors in CMS.
–> Multi server administrator.
–> MDS [Master Data Services].
–> “Data-tier” applications
–> POWPIVOT for virtualization
–> Full support to

  • Ms-excel 2010.
  • Ms visual studio 2010.
  • Full support to SharePoint server
  • Visualization…etc.

–> Data compression with UCS-2 code support.
–> Available edition
i) Data centre edition

  • Huge data storage
  • Recommended for OLTP, ODS storages.

ii) Parallel DWH edition

  • Huge data storage
  • Recommended for BI applications.

2008R2 to 2011:
èIts code name “DENAIL”
èMulti sun-net failover clustering introduced.

Programming enhancements:
Creating sequence introduced.
Syntax: create sequence START WITH increment by . Eg: create sequence x start with 1 increment by 1. Insert into test values (Next value for x, ”vinay” –> emp ID or username)

Paging implement in 2011:

  • It display the required rows in page wise.
  • Full text search of index introduced.
  • The usage of excel power point pivot enhanced so that reporting models are created easily.
  • Analysis service “BISM” (Business Intelligence Semantic Model) introduced. It is a 3-layer model.
  • WEB based visualization (project crescendo introduced) it is a code name for representing application for better visualizations.
Explore SSIS Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

List of Related Microsoft Certification Courses:

 SSRS Power BI
 SSAS SQL Server
 SharePoint BizTalk Server
 Team Foundation Server BizTalk Server Administrator


Course Schedule
MSBI TrainingJul 27 to Aug 11View Details
MSBI TrainingJul 30 to Aug 14View Details
MSBI TrainingAug 03 to Aug 18View Details
MSBI TrainingAug 06 to Aug 21View Details
Last updated: 03 Apr 2023
About Author

Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.

read less
  1. Share:
MSBI Articles