Featured Database Articles

Introduction to Control Flow and Data Flow of SSIS 2008

Learn about two primary components of SQL Server 2008 Integration Services packages and their graphical representation in the Business Intelligence Development Studio

In our most recent article dedicated to
the subject of Integration Services in SQL Server 2008 and 2008 R2, we
described the
process of executing a sample package created using Export and Import Wizard, which
copied content of a view in the AdventureWorksDW database to a flat file. As part of
this presentation, we imported it into a new project (based on the Integration Services Project template)
within Business
Intelligence Development Studio. Since our primary purpose for using this utility
is designing SQL Server 2008 Integration Services-based packages, we will now
focus our attention on this aspect of its functionality.

As we start exploring this subject, it
is important to clarify relatively common confusion regarding two of the most
fundamental concepts that deal with package design - namely control flow
and data flow. Control flow deals with orderly processing of tasks, which are
individual, isolated units of work that perform a specific action ending with a
finite outcome (such that can be evaluated as either Success, Failure, or Completion). While their
sequence can be customized by linking them into arbitrary arrangements with precedence constraints and grouping
them together or repeating their execution in a loop with the help of containers, a subsequent
task does not initiate unless its predecessor has completed. Data flow, on the
other hand, handles its processing responsibilities by employing the pipeline
paradigm, carrying data record by record (or rather, to be more accurate,
memory buffer by memory buffer) from its source to a destination and modifying it in transit by
applying transformations. (There are
exceptions to this rule, since some of them, such as Sort or Aggregate require the
ability to view the entire data set before handing it over to their downstream
counterparts). Note that this does not imply that tasks cannot be executed in
parallel, but rather that if they do, their actions are not coordinated (unlike
processing of data flow components that are part of the same data stream).
Another distinction between them is the absence of a mechanism that would allow
direct transfer of data between individual control flow tasks. On the other
hand, data flow lacks nesting capabilities provided by containers.

These two SQL Server Integration Services
features are implemented in the form of two tabs (bearing their respective
names) of the Designer interface in
the Business
Intelligence Development Studio. The control flow portion of a package is
constructed by populating the area exposed by the first of these tabs,
typically by dragging tasks and containers delivering desired functionality
from the Toolbox. The same
methodology is applied when adding data sources, destinations, and transformation to the area exposed by the Data Flow tab (with Toolbox adjusting its
content depending on the context).

If you open our sample package created
using Import
and Export Wizard,
which we imported into Business
Intelligence Development Studio and display its logical layout by selecting the View Designer entry in its
context sensitive menu, you will notice that the Control Flow tab contains only a single item
labeled Data
Flow Task 1.
Effectively, the package consists only of a single Data Flow task, whose content
you can view by double-clicking on it. This action will automatically activate
the Data Flow tab, exposing,
in this case, a single source (representing the vTargetMail view in AdventureWorks database) and a
target (designating the text file to which we load extracted data), connected
via OLE DB
Source Output Data Flow Path.

Let's analyze characteristics of these
three components in more detail. Display the OLE DB Source Editor dialog box by
selecting the Edit... option from the
context-sensitive menu of our data flow source. Its first section, labeled Connection Manager, identifies
data origin and the method of accessing it, which in our case, relies on the [dbo].[vTargetMail] view and references
the SourceConnectionOLEDB entry appearing
on the Connection
Managers
tab (located at the bottom of the Designer pane). The second section, titled Columns, provides a
listing of Available
External Columns
(those defined in the [dbo].[vTargetMail] view), allowing
you to selectively exclude them (if desired), as well as alter default mappings
to their respective output columns (which you also have the ability to rename).
In order to fully realize the significance of this option, it is important to
realize that (as we pointed out earlier) data flow operates by streaming
records between two (or more) endpoints. In our example, they originate from a
view in the AdventureWorks database,
represented by External
Columns.
The responsibility of a source is to extract
this external data and pass it along via its Output Columns to Input Columns of a downstream
component (such as a transformation or a destination). This implies
that a source must have at least one output, a destination needs at least one
input, and a transformation requires at minimum one input and one output. You
can verify this by reviewing the Editor interface of our Flat File Destination component, whose
second section, labeled Mappings, represents the
relationship between Input
Columns
and Destination
Columns.

Another way to view this particular
functionality involves the use of Advanced Editor, accessible for all components by
selecting the Show
Advanced Editor...
entry from their context sensitive menu. The resulting dialog box is divided
into four tabs, labeled Connection
Managers
(listing all of its connection managers), Component Properties (which mirrors the content of
the Properties window), Column Mappings (equivalent to Columns section of the Editor dialog box we
described earlier), and Input
and Output Properties.
By referencing that last tab, you will be able to quickly evaluate the number
of inputs and outputs, their columns, and identify individual characteristics
of each.

Note that the majority of components
also contain an additional output intended for capturing errors, which in our
case is configurable via the Error Output section in the Editor dialog box of the OLE DB Source component (and
available for viewing via OLE DB Source Error Output entry on the Input and Output Properties tab of its Advanced Editor). Its interface
gives you the ability to designate one of three actions (Ignore failure, Redirect row, or Fail component) that will be
triggered in case of an Error or Truncation for each of the
input or output columns.

Our source and destination are linked by an OLE DB Source Output Data
Flow Path.
Its Editor interface is
divided into three tabs. The first of them, labeled General, gives you
access to all the path's properties. The second, named Metadata, enumerates the columns
that data flowing between adjacent components comprises of, listing values of Name, Data type, Precision, Scale, Length, Code Page, Sort Key Position, Comparison Flags, and Source Component properties for
each (which provides valuable information in case you run into problems caused
by mismatches between source and destination columns). The last of them, called
Data Viewers, is also
extremely useful in troubleshooting or testing data flow by allowing you to
examine its content in transit. The viewer opens at runtime and displays
passing records one buffer at a time, halting execution temporarily until you
decide to proceed to the next one (it is also possible to copy its content to Clipboard or detach the
viewer, letting the transfer complete without further interruptions). You can
choose between four viewer types including:

Grid (presenting columns you specify in
a tabular format)

Histogram (intended for viewing distribution
of numeric values only)

Scatter Plot (x,y) (using a
pair of numeric columns to express their relationship in x and y axis
layout)

Column Chart (calculating occurrences of
identical values within a selected column and displaying them side by
side).

To test this functionality, use the Add... command button
in the Data
Viewers
section of Data
Flow Path Editor.
In the resulting Configure
Data Viewer
window, select the desired viewer type and configure its properties (by picking
columns you want to monitor).

Our package can be launched either
within the programming environment provided by Business Intelligence Development Studio or outside of
it. The latter of these two choices requires use of the Start Without Debugging entry in the Debug menu (equivalent
to the Ctrl+F5 key
combination). The former can be carried out by pressing the F5 key, clicking
on the button with a green triangle in the Standard toolbar, selecting the Start Debugging entry in the Debug menu, or
activating the Execute
Package
option in the context-sensitive menu of the package entry in the Solution Explorer window. This
method will provide graphical clues representing current execution status, with
a yellow background of active tasks and components indicating operation in
progress, green signifying successful completion, and red designating a
failure. In addition, you should see a dynamically generated label appearing on
the upper edge of the destination component on
the Data Flow tab, which
represents the total number of processed rows. You also have an option to view a
detailed description of individual steps that are taking place throughout
package execution by switching to the Progress tab. At this point, you can return to the
design interface by selecting the Stop Debugging entry from the Debug menu, pressing
the Shift+F5 key
combination, or clicking on the button with a blue rectangle in the Debug toolbar (note
that this will automatically change the label of the last tab of the Designer interface from Progress to Execution Results).

Clearly, our sample package has a very
rudimentary structure, reflecting it wizard-based origin and rather simplistic
purpose. As we have demonstrated, while our current approach to package
authoring is quick and straightforward, it lacks flexibility and is rarely
sufficient to carry out more complex ETL tasks. In our next article, we will
focus on more elaborate scenarios (starting with importing and exporting
Excel-based data) and the unique challenges they introduce.