SSIS Architecture

  • Packages – A package is a collection of tasks framed together with precedence constraints to manage and execute tasks in an order. It is compiled in a XML structured file with .dtsx extension.
  • Control Flow – It acts as the brain of a package.It consists of one or more tasks and containers that executes when package runs. Control flow orchestrates the order of execution for all its components.
  • Tasks – A task can best be explained as an individual unit of work.
  • Precedence Constraints – These are the package components that connect the tasks together and manage the order in which the tasks will execute.
  • Host Containers – The Control flow tab (window) is itself host Container.
  • Containers – Core units in the SSIS architecture for grouping tasks together logically into units of work are known as Containers.
  • Connection Managers – Connection managers are used to centralize connection strings to data sources and to abstract them from the SSIS packages. Multiple tasks can share the same Connection manager.
  • Data Flow – The core strength of SSIS is its capability to extract data into the server’s memory (Extraction), transform it(Transformation) and write it out to an alternative destination(Loading).
  • Sources – A source is a component that you add to the Data Flow design surface to specify the location of the source data.
  • Transformations – Transformations are key components within the Data Flow that allow changes to the data within the data pipeline.
  • Destinations – Inside the Data Flow, destinations consume the data after the data pipe leaves the last transformation components.
  • Variables – Variables can be set to evaluate to an expression at run time.
  • Parameters – Parameters behave much like variables but with a few main exceptions.
  • Event Handlers – The event handlers that run in response to the run-time events that packages, tasks, and containers raise.
  • Log Providers – Logging of package run-time information such as the start time and the stop time of the package and its tasks and containers.
  • Package Configurations – After development your package and before deploying the package in production environment from UAT you need to perform certain package configurations as per production Server.


Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |


Difference between DTS and SSIS

The DTS and SSIS are both used in SQL Server Transformations. The DTS was used for transformation purpose up to SQL Server 2000 and SSIS (Including SSAS and SSRS) are the advanced to DTS and are included in SQL Server 2005 onwards. The capability of SSIS tool in comparison with DTS is tremendous. This SSIS tool is in competition with other ETL Tools in the present. The SSIS has lot added features as per the current requirements.


· DTS stands for Data Transformation Services.

· DTS is a set of objects using an ETS tool to extract, transform, and load information to or from a database

· DTS was originally part of the Microsoft SQL Server 2000

· Uses Active-x Script

· No Deployment wizard is available

· Limited Set of Transformation available

· Does not support BI Functionality

· Single Task at a time

· It is unmanaged script

· DTS can develop through Enterprise manager

· We can deploy only at local server

· Designer contains Single Pane

· No Event Hander

· No Solution Explorer

· Connection and other values are static, not controlled at runtime.


· SSIS stands for Sql Server Integration Services

· SSIS is an ETL tool provided by Microsoft to extra data from different sources.

· SSIS is a component of the Microsoft SQL Server 2005

· Uses Scripting Language

· Deployment wizard is available

· Huge of Transformations available

· Completely supports end to end process of BI

· Multi Tasks run parallel

· It is managed by CLR

· SSIS can develop through Business Intelligence Development Studio (BIDS, nothing but new version of VS IDE)

· It can be deployed using multiple server using BIDS

· SSIS designer contains 4 design panes:
a) Control Flow
b) Data Flow
c) Event Handlers &
d) Package Explorer.

· Event Handler Available

· Solution Explorer is available, with packages, connections and Data Source Views (DSV)

· It can be controlled dynamically using configuration

Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

Need of SSIS

List of things SSIS does that SQL server either does poorly or not at all:

  • Connections to a vast number of datasources – flat files, other RDMS systems, XML sources, etc.
  • Complex error handling within dataflows – start and stop dataflows based on severity of the error. Throw an email to admins/DBAs when on error. Pick up the dataflow mid-stream after the error has been resolved.
  • Dataflow auditing – Easily check and redirect data that does not meet/match certain criteria – get an email when a certain number of rows in a given process have no matches found. Easily collect statistics on dataflow processes
  • Visual/logical representation of dataflows as well as logical modularity of dataflows

Way too often, we hear many misconceptions about what the Integration Services Service actually does. Some think it is required to execute packages. Others think it is used for checkpoint restarts. Others think it speeds up execution. So, which group is correct?

The answer is none of them. The SSIS Service, quite simply, is responsible for managing the Integration Services interface in SQL Server Management Studio. It enables the ability to import/export packages, view running packages, and view stored packages. It really doesn’t do anything more than that.

I guess it depends on what you are doing. SSIS is very powerful, just like old DTS. If you are loading lots of items and expect to have constant change, I would go SSIS all the way. If you are looking to load only a few items and it’s for lots of customers, I would put it in code. I prefer SSIS for in house ETL processes, but I use .Net at client shops when I need to load data from a legacy system into a SQL database. Now as I stated before if you have a lot of transformations and lots of different data silos to load, I think you would be crazy to do this in .Net and I would go SSIS. If you have only a few items to load and it’s for a single application and may be installed as part of an application at various clients, I would go .Net all the way. Just my 2 cents.

I don’t think you are entirely wrong, however you seem a bit misguided. SSIS is not intended to replace the Database Engine but meant to compliment it and enhance it. It’s very effective as an ETL tool. For instance, let’s say you wanted to extract a list of clients from one of your database systems and email that list of data to a Direct Mail house every week. With SSIS this is a very easy thing to put together. There’s an easy means of transferring data from SQL Server to a file (CSV, Excel, etc) and sending an email.

Additionally SSIS is nice in that it has the Script Task which will allow you to write VB Script to do a variety of things. From my history I’ve used it to perform data access and send data from one system to another. This will effectively operate the same as if you were to have an application doing the data transfer… but the great thing about SSIS is that you can deploy the package and schedule it run with the SQL Server Agent.


Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |