Madhivanan's TSQL Blog

Exploring SSIS - Understanding the basics

SQL Server Integration Services (SSIS) is one of the tools that can be effectively used to transfer data to various destinations. In versions prior to 2005, we had Import/Export wizard which was part of Database Engine and a package would be created based on this. But from 2005 onwards SSIS comes as a seperate package along with SQL Server. This is built on top of Visual Studio (.NET) and known as Business Intelligence Development Studio (BIDS) that allows more powerful way of transforming data. Although this is primarily used for ETL(Extract, Transform and Load) purpose, a lot more can be done.

SSIS has following sections

1 Control Flow2 Data Flow3 Event Handler4 Package Explorer

We will breifly see what these do

1 Control FlowThis controls the entire flow of SSIS pacakge. We can make use of various Control Flow items available like Execute SQL task, File System task, FTP task, Send mail task, etc. The Execute SQL Task can be effectively used to execute Stored Procedures, delete/truncate tables, or perform other DML statements. We can have many tasks in this Flow and order them so that the tasks are executed in the order they are specified. The ordering can be done by using the Precedence Constraint ie when you see Green Arrow for a task, you can drag it to connect to the next task that should be executed

2 Data flowThis is primary used to connect to various sources (Database Servers, Flat files,etc) and load them to different destinations. The items of this task are categorised into three (Sources, Transformation and Destinations).

3 Event HandlerThis responses to the events raised by the executable items like data flow task, for each loop, etc. The event can be executed to perform various tasks like sending email notification if one of the tasks is failed, clearing staging tables, etc