SSIS USER INTERFACE

Hope my last article gave a high level idea about the internal architecture of SSIS.

Today we shall focus on SSIS User Interface (SSIS Designer) where we develop the SSIS packages.

Here is how we can navigate to the SSIS Designer.

START>MS SQL SERVER 2008>SQL SERVER BUSINESS INTELLIGENCE DEVELOPMENT STUDIO and Click on File>New>Project>Integration Services Project

Below is quick peek at the SSIS designer and also look at the different components in the pane.

Tool Box – Tool Box consists of various tasks which are specifically designed to perform some unit of work. Related tasks are grouped into categories.

Examples for Control flow Tasks - Execute SQL Task which is used to execute SQL Queries, Send Mail Task.

Package Execution button- Package Execution tab is used to execute the package from the SSIS Designer.

Connection Manager Pane – Connection manager pane is part or area where we define the connection managers to be used for the Package. The selection of the connection manager purely depends on the requirement. For e.g. if we are pulling the data from flat file(source) and pushing it into the SQL server table(Destination) then we have to define two connection manager a) Flat File Connection Manager for source b)OLEDB Connection Manager for destination. Connection Manager primarily contain the Connection string to the file/database.

Data Flow Task- Data flow Task is where we define the actual flow of the data from source to destination.

Properties- Properties pane allows us to set the properties of package object. For e.g. in the Description tab we can define the job performed by a particular package. It also gives the information like unique ID of package.

Enumerator – Enumerators are one of the control flow task which are used to loop through the object of same kind. For e.g. in the above diagram we have used For Each Loop enumerator which will process the all files in the same folder.

Task- Task is the unit of work that is performed by a package. They are categorized into Control flow and Maintenance Plan Task. Under Control Flow task we have several sub tasks for e.g. File System task can be used to copy files from one location to other. Similarly in the Maintenance Plan Task we have Backup Database task which is used to take the backup of a database.

Solution Explorer Pane – Solution explorer gives the hierarchal view of all the Projects and Data Sources within the solution and also the Packages within the respective Projects.

Control Flow Pane –Here we define our control flow tasks. We can select the Control flow task(as per our requirement) from the tools list, drag it and drop it to the control flow pane. The different tasks in the Control Flow are connected by unidirectional arrows called Precedence constraints.

Data Flow Pane – Data flow pane is the area where we define the actual flow of the data. The three major components involved here are a) Source Component b) Transformations c) Destination component.

Event Handler Pane – Event handler pane is the area where we define what needs to be done on specific events during the execution of package.

For e.g. If we need to intimate the Support Team in case of a failure of package, then we shall define a Send Mail Task in the Event Handler and configure it to be triggered “On Error”(Event).

Package Explorer Pane – Package explorer pane gives the hierarchal view of all the objects in the package like connections, event handlers, variables, log providers etc.

In the coming articles we shall explore some of the Panes in more detail and shall focus on the components involved in them.

Happy Learning !!!