Tuesday, June 30, 2015

SSIS Tutorial : Understanding the Basics

SQL Server Integration Services (SSIS) is one of the tools that can be effectively used to transfer data to various destinations. 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.
Before 2005 it was DTS with very limited features.

SSIS has following sections
1 Control Flow
2 Data Flow
3 Event Handler
4 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 flow
This 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 Handler
This 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
4 Package Explorer
This shows the entire list of package items like variables, connection managers,event handlers, executables, etc

No comments:

Post a Comment