SSIS Designer encourages reuse

SQL Server Integration Services provides several features of modularization to allow you to organize your packages so that they work efficiently and can be maintained easily. For instance, the SSIS Designer has four distinct tabs: Control Flow, Data Flow, Event Handler and Package Explorer. This allows you to focus on a part of the package at a time and encourages re-use of components. In DTS 2000 we had one workflow for all processing including data flow and event handling so the iconic diagram became very messy very quickly.

I call the Control Flow tab the “Happy Day scenario”. This shows us what the package should be doing on a good day. In other words, a high-level view of the tasks that the package executes under normal circumstances and in what order. All the error processing and event handling can be hidden away under the Event Handler tab. By implication, that tab holds the “Unhappy day scenario” although it can be used for auditing in addition to standard error processing. Either way, it does not clutter our view of the Control Flow when we are working on that part of the package.

The Data Flow tab provides a diagram for each Data Flow task in the package, the most popular task in SSIS. Again, this allows the Control Flow to be simplified, as each Data Flow task is represented by a single task object. Just double-click the Data Flow task (or right-click and Edit) and the SSIS Designer will switch to the Data Flow tab for that task to show all the detail hidden within that task. Again, this modular approach encourages re-use.

The Event Handler tab can use the same types of tasks and containers as are used on the Control Flow, but each event has its own flow diagram, again hidden away until you need to see it. You could write data to an audit table or send an email based on a particular event including the commonly specified “OnError” event.

The Package Explorer tab gives an alternate view of the package in a tree structure format similar to Windows Explorer. For large packages, this allows us to navigate quickly to a particular task. Double-clicking a task (under Executables) will take us to that object in the package.

When we are debugging, SSIS Designer does provide a fifth tab for Progress. This will show us the major milestones of the package execution as they happen and can be very useful when errors occur during testing.

Call me an optimist, but I like working on the Control Flow without having to worry about the error processing until later in the project. That keeps me happy.

Cheers

Brian

In this article

Join the Conversation