Overview of projects and solutions in SSIS

By: Ray Barley

Overview

An SSIS package is similar to a program that performs some specific task.  As an example of something we might do with an SSIS package, I would like to copy the contents of a table in the AdventureWorksDW database to an Excel spreadsheet.  We initially did this using the Export Wizard task in SSMS and saved our work as an SSIS package.  We will review that SSIS package as a way to get an understanding of an SSIS package.

Explanation
To begin launch BIDS by selecting SQL Server Business Intelligence Development Studio from the Microsoft SQL Server program group.  Click File, Open, Project / Solution on the top level menu to display the Open Project dialog.  Navigate to the location of the solution as shown below then click Open:

Expand the SSIS Packages node under the SSMS-Samples project in Solution Explorer and you will see the following:

Double click on the SSIS package SSMS-Export-to-Excel.dtsx to open the package.  Let's walk through the following aspects of the SSIS package as displayed in BIDS:

• Designer
• Connection Managers
• Toolbox
• Properties Window

The designer is the large area in the middle of the window and is shown below:

The following tabs are available in the designer:

• The Control Flow tab (shown above) contains the tasks that the SSIS package performs and the flow from one task to another.
• The Data Flow tab (shown below) is another designer that contains the details for a given data flow task; e.g. retrieve data from some data source, optionally perform some transformations on the data, then write it to some other data source.
• The Event Handlers tab is yet another designer where we can specify tasks to be performed when a particular event is raised.
• The Package Explorer tab represents the entire package in a tree-view.

The following is the Data Flow designer for the Data Flow task in the Control Flow tab as shown above:

The Connection Managers window contains the various data sources and destinations that the package uses:

The Connection Managers are defined once then referenced in the various tasks such as the Execute SQL Task, an OLEDB Data Source, or a OLEDB Destination.

The Toolbox contains the tasks that are available to the Control Flow, Data Flow or Event Handlers designers.  To build a package you simply drag tasks from the Toolbox onto the designer and connect them in the order you want to execute.  The following Toolbox tasks are available in the Control Flow designer tab:

The following Toolbox tasks are available in the Data Flow designer:

The Properties Window is available for us to edit and update the properties of a task in the designer, or a connection in the Connection Managers area.  Click on the Preparation SQL Task in the Control Flow shown above and you will see the following in the Properties Window:

The Preparation SQL Task is an Execute SQL Task and is used to execute a SQL statement.  In this case we execute a CREATE TABLE statement but it could be any SQL statement or stored procedure.  When you right click on an object you get a popup dialog that you can use to set the various properties; e.g. right click on the Preparation SQL Task and select Edit from the menu and the following multi-page dialog will be displayed: