Explain SSIS Designer for Newbies

This article is about how to create and maintain integration service packages using SSIS Designer.

What is SSIS Designer?

SSIS Designer is a graphical tool that we can use to create and manage integration service packages. We can open SSIS Designer in SQL Server Data Tools (SSDT). SSIS designer consists of 6 permanent tabs/components: Control Flow, Data Flow, Parameters, Event Handlers, Package Explorer Connection Managers, etc. When we run any package, the 7th tab appears named "Progress" and is automatically added to the SSIS designer. Once the package stopped running, the "Progress" tab was renamed to "Execution Results" automatically.

Below is the screenshot showing all six tabs highlighted in red on SSIS Designer:

Below is the screenshot when the SSIS package was running, and the 7th tab appeared named "Progress" highlighted in red in the designer.

Below is the screenshot of when the SSIS package stopped running, and the Progress tab was renamed to "Execution Results,” highlighted in red in the designer.

Now, let's explore the functions and features of each tab/component that is available in an SSIS designer. Understanding each component of SSIS design will enable you to utilize its capabilities and help create efficient data integration solutions.

Control Flow

In SSIS Designer, control flow is the visual representation of how tasks and containers are displayed as icons and arrows, which provides a user-friendly interface for designing and managing the package workflow. It is the backbone of the SSIS Package, which controls the flow of data/operations and executes tasks in a defined order, implements logic conditionally or in a loop, and handles errors and exceptions.  

Data Flow

In SSIS Designer, Data Flow refers to the visual representation of how data moves from source to destination and transforms/cleans/modifies within a package. It's the core functionality of the SSIS designer where we build the data pipeline. We can build one or more Data Flow tasks within the SSIS package, and this can be implemented within the Control flow.

Parameters

In SSIS Designer, the Parameters tab allows the definition and management of parameters that can be used in the SSIS package. We can create new parameters, edit and manage existing parameters, we can define data types and default values etc. The parameter tab also enables the parameter value to be passed during package runtime. We can make the SSIS package dynamic and flexible by using the parameter tab.

Event Handler

In SSIS Designer, the Event Handler tab allows you to respond to events that occur during the execution of an SSIS package. Events can be triggered by the success or failure of any task. We use an event handler in SSIS Designer to send notifications, log events, and handle errors and exceptions.

Package Explorer

In SSIS Designer, Package Explorer is used to view the contents of a package. By using this tab, we can arrange the package's structure.

Connection Manager

In SSIS Designer, Connection Managers are used to add and configure connections to source and destination. It simplifies the process of managing and reusing connections in the SSIS package. It eliminates the need to repeatedly put the same information if we already defined connection details like server name, database name, and authentication information in one place.

Progress

In SSIS Designer, the progress tab appears at run time, which shows the execution progress of the package while running and disappears once the execution result finishes.

Conclusion

We have gone through the SSIS designer tool that is being used for designing and managing data integration and workflow applications. It provides a user-friendly interface for creating complex data integration tasks, which is used for building ETL pipelines.

Hope you liked the article, please share your feedback/ suggestions in the comments section below.