SSIS Wizards and SSIS terminology

In this article I will explain briefly about SSIS wizards (import and export wizard and BIDS Environment) and SSIS terminology

ETL: ETL stands for Extract,Transform and Load. SSIS is an ETL tool and used to extract data from multiple sources transform into required format and then load into destination.

Import and Export Wizard: Import and Export wizard is a tool provided with SQL server and used to import data from any OLE-DB source complaint data source to destination. This wizard is a quick way to transfer data from source to destination with minimal transformations.

Business Intelligence Development studio (BIDS)/SQL server Data Tools (SSDT): BIDS/SSDT is central environment where you will spend most of your time to create SSIS projects. This environment provides all the features to create complex SSIS projects.

Solution Explorer: A solution explorer is a container that holds many projects. For example you have solution that may contain many projects such as ASP.NET project, SSIS project, SSRS project and SSAS project. By default Solution explorer will not be shown if only single project exists. To enable this feature Go to Tools->Options->expand Projects and solutions select Always show solution under general tab.

Project Explorer: New project will be added to existing solution explorer by right click on solution explorer window Add -> New Project. By default SSIS Related project contains single package under SSIS packages folder and 3 other folder Data sources, Data source views and Miscellaneous when the project created.

Data sources: A data source allows you to create data connection that can be used to perform ETL operations by whole package. Data source connections can be OLEDB, ODBC, ADO.NET, and DB2. Data sources can be either by single package or all packages in the Project.

Data sources Views: Allows you to create logical views of your business data. These views consist of few tables or views that can shared either by one package or all packages in SSIS Project.

SSIS Packages Folder: This contains all packages

Miscellaneous folder contains any other files added to our project other than Data source, Data source views or SSIS packages.

.dtsx: An SSIS package extension

.ds: A shared Data source extension

.dsv: A shared Data source view extension

The Toolbox: The tool box contains all items that can be used in designer pane. Tool box in Control flow designer pane displays all tasks and Tool box in Data flow designer pane displays sources, Transformation and destination.

Properties Window: Any task or selected item can be customized using properties window. Select any particular task and press F4 to display properties window.

SSIS Terminology:

Package: A package is core component in SSIS and an executable program that contains work flow and business logic. A package is an unit of execution much like .NET program. Package in SSIS created with .DTSX extension which is XML structured file.

Package is a collection of tasks which are connected by precedence constraints.

Control Flow: The main important component in SSIS package is control flow and control flow is collection of tasks which are connected by Precedence constraint. Control flow contains work flow of package which consists of containers, tasks and precedence constraints.

Tasks can be dragged from tool box on to control flow design pan, yellow warning or red error will be shown on the task until you configure the task as shown in the below figure.

Task: A task is discrete unit of work which provides functionality to SSIS package. SSIS provides set of tasks. Read tasks article to know more about SSIS tasks

Precedence constraint: Precedence constraint is used to execute the tasks in specific order and which defines work flow of the package. A constraint controls the execution of two linked tasks by executing the destination task based up on final state (Success, Failure, and Completion) of prior task and business rules that are defined by expressions. You need to specify constraint or Expression in Precedence constraint editor as shown below

Evaluation operation specifies either constraint or expression or both.

There are 3 constraints values.

1. Success: A task that’s linked with this constraint executed only prior task is successful. This task is colored in Green

2. Failure: A task that’s linked with this constraint executed only prior task is failed. This task is colored in Red

3. Completion: A task that’s linked with this constraint executed only prior task execution is completed whether or not if prior task is succeeds or fails. This task is colored in Blue

Expressions can also be specified along with constraints.

Annotation: An annotation is comment in SSIS package. To add annotation Right click on package design pane and select Add annotation. Annotations are used to explain others about your package.

Connection Mangers: Connections manager tab is placed at the bottom of the package which contains all connections required for control flow and data flow in package. Connections can be FTP connection or HTTP connection or OLEDB connection or flat file connection.

You can create connection by right clicking in the connection manager tab and choosing appropriate connection.

Variable: Variable allows you to create dynamic SSIS packages and which are much like variables in other programming languages. Variable in SSIS are classified into two categories.

System Variable system variables are pre-defined variables in SSIS such as machinename, Containerstarttime, PackageName, PackageID and all system variables are prefixed with System as System::<VariableName>

User Variable user variables are created in package and all user variables are prefixed with User as User::<VariableName>. The scope of user variable can be task level, container level, event handler or entire package.

To access variable window, Right click on deign pane and select variables. Grayed colored variables are system variable and blue colored variables are user variables.

NOTE: Scope cannot be changed once defined while creating user variables variable.

Data flow: Data flow is another import component in SSIS after control flow. In data flow tab, you will perform core functionality of SSIS i.e. extract data from different sources, transform it and then write into destination.

Data flow is one task in control flow. When data flow task is created, subsequent data flow task will be created

Example: in the below example 3 data flow tasks are created. Each data flow task has its own data flow designer pane separately as shown below.

Event Handler: Event handler tab is used to handle errors or warnings or completion in task or container.

Package Explorer:

Package explorer consolidates all information including variable, precedence constraints,Event handler, Connection managers, Log providers and executables.