SSIS Package Tutorial

SSIS Package Introduction

In SSIS Package is a combination of Connection Mangers, Control Flow Tasks, Data Flow Elements, Parameters, and Event Handlers that you use to perform ETL operation.

SSIS Package is all about extracting data from different sources transform it and then load it in completely different destination.

Create SSIS Package

When you create a New Project, BIDS will automatically create a new package for you. However, you have an option to create new package.

To do so, right-click on the SSIS Packages folder, and select New SSIS package option from the context menu

This will create a new package as shown in below screenshot

SSIS Package Tutorial

If you observe the below screenshot, each package has six different tabs.

Connection Managers

We need connection manager to establish the connection between SSIS package and source, and package and destination. I suggest you to refer Introduction to Connection Managers article to understand them.

Control Flow Tasks

A package should contain at least one control flow element. Control Flow is a combination of Containers, Tasks, and the Precedence Constraints.

Containers

For Loop and Foreach Loop containers works similar to For Loop and Foreach loop in any programming language. Sequence container helps to group multiple tasks.

Tasks

Control Flow Tasks are used to work with local files, FTP files, Web Services, Scripts etc. You can use these tasks to perform Database administrative tasks as well.

Precedence Constraints

Use Precedence Constraints to control the tasks flow. For example, you can write qualified expressions that has to meet before executing any task.

Data Flow Elements

Data Flow tab is a combination of Sources, Transformations, and Destinations. By default, you cannot work in the Data Flow tab. Therefore, before you start working on the data Flow tab, you have to add data Flow Task in the Control Flow tab.

From the below screenshot, you can observe that there are three sections, and a Favorite section:

Favorite section

If you do not know the basic steps involved in connecting with Source, or Destination then you can use Source Assistance, and Destination Assistance. These two provide a nice and easy Wizard. By using this wizard, you can establish connection with Source and Destination.

Sources

Source is the place from where you want to extract data. SSIS package supports wide variety of sources such as Excel Files, Flat Files, XML Files, Relational databases

Transformations

List of transformations that are available under this section are used to transform (slice and dice) data as per your requirement. For example, you can Pivot Data, Merge 2 tables, Sort tables etc

SSIS Destinations

Destination is the place where we load the final data (after transformation). SSIS package supports wide variety of destinations such as Relational databases, OLE DB, ODBC destinations, Excel Destination, Flat Files Destination, XML Destination, etc.

Parameters

Parameters helps you to modify the package without modifying the entire package and replotting it. There are two types of parameters in SSIS and they are Project Parameters, and Package Parameters. I suggest you to refer SSIS Parameters article to understand the process of creation of parameters.

Event Handlers

This tab is useful to handle the raised events in the run-time. For example, if you want the package to send an email to manger after the package executed then you can create a script task for the onPostExecute event. I suggest you to refer Event Handlers in SSIS article to understand the process of configuring events.

Package Explorer

This tab will display all the elements in a package that includes: Connection Managers, Tasks, Parameters, Event Handlers, Variables etc.