Data Flow

Introduction

Data flow is where all data extraction, transformation, mapping, and loading happens. Before starting to develop your data flow logic, you need to tell the control flow what you are going to do with it. So your first job is to add a data flow task to the control flow.

As I have previously explained, one package can only have one control flow; however, it can have as many data flows as you want. However, in the SSIS Designer, there is only one Data Flow tab. This is because it is a dynamic tab; whenever you click on a data flow task, this tab will assume the objects developed in it. This way, if you add a data flow task to your control flow, you can double-click it or select the Data Flow tab to open the data flow designer.

57. Opening the data flow designer

Data flow objects can be of four different types: sources, transformations, destinations, and paths. An interesting fact about data flows is that when you retrieve data from a source, the transformations, destinations, and paths that come after the initial source object will be executed in memory, which makes it a powerful engine.

Now, let’s take a look at what these objects do. Sources are connections from which you will retrieve your data. Destinations are connections to the targets in which you want to insert or update data. In a perfect world, the schemas of source and destination would be the same but, as you know, they are not. Because of this, transformations are important because they allow you to transform the data to follow the destination schema. The transformations allow you to perform operations against your data sets such as column conversions, column creation, and many more that will be explained in the sections that follow.

Last but not least, we need to connect all these objects. For that we use paths, which are similar to precedence constraints but with fewer configuration options. However, they exist for the same purpose: to connect objects inside the data flow and define when to take flow (Failed, OnMatch, and NonMatch). The constraints of paths will be explained later.

When you connect two objects, the resulting behavior is a little bit different from the control flow. This is because, in this case, the connection doesn’t define the conditions for the next task to be executed. Rather, it defines where the data will flow next when the execution of a transformation fails, when it succeeds, or some other option. This means that, when you make a connection, the information is about when or where the columns available to work in this data flow become available in the next component of the data flow path. Like the control flows, these objects are designed using a drag-and-drop designer and then double-clicking them to open their configuration options. Now let’s take a look at all available objects.

58. SSIS Toolbox with data flow objects

The first step when developing data flows is to define the source of your data. You can have as many sources as you need, and the SSIS data flow designer allows you to later join or merge that data (for instance, to insert it into a single destination). All of the objects available in the data flow toolbox are organized by their groups and explained in the sections that follow.

Favorites

The Favorites group contains two of the most important objects; they allow you to connect to external data sources or destinations. You can add new objects to this group by right-clicking an object and selecting Move to Favorites.

5. Data flow transformations in the Favorites group

Common

The Common tasks group includes objects commonly used in data flows. Once again, you can move objects to this group by right-clicking them and selecting Move to Common. The following table explains each of the objects that you can find in it.

6. Common data flow transforms

Other Transforms

The Other Transforms group includes objects less frequently used in data flows. Although they are not used as often, they can be equally as important to your ETL projects.

7. Other Transforms

Other Sources

The Other Sources group includes other source objects used in data flows.

8. Other Sources

Other Destinations

The Other Destinations group includes other used destination objects in data flows.

9. Other Destinations

Data Viewers

Introduction

Data viewers are great debug components because they allow you to see the data that is flowing from one data flow component to another. As I wrote before, paths connect data flow components by connecting the output of one data flow component to the input of another data flow component. Data viewers operate in these paths, allowing you to intercept the “communication” and watch what input will be used in the next component or in another point of view (with the data set that the previous component originated). When you run the package, it will stop that data viewer and only continue its execution when you tell it to. By using data viewers, you can evaluate whether or not the result of a data flow component meets your requirements. If not, it allows you to correct it.

An important but logical prerequisite of using data viewers is that you must have at least one data flow in your package control flow and, inside the data flow, at least two components connected.

Using Data Viewers

To use a data viewer, right-click the path you want to evaluate and then select Enable Data Viewer as shown in the following figure.

59. Enable Data Viewer

And that’s it. If you run a package with a data viewer in a data flow, the result will look like the following figure—a window showing the data being moved in that particular path.

60. Data Viewer Result

Creating a Simple Data Flow

Introduction

To show you how these components work, we will create a simple data flow that gets a data set from a SQL Server database, transforms the data according to the schema in the destination SQL Server table, and then inserts it. It’s a simple data flow that will allow us to work with these objects for the first time. The components we will use are:

• Source
• Conversion
• Derived Column
• Destination

Creating the Data Flow

To create this example data flow, we start by dragging and dropping each object we need into the data flow designer and, after linking them, making all the configurations needed. So let’s start by defining the flow.

61. Simple Data Flow

Now, the way you develop a data flow is always the same: add the objects you need to the data flow designer, connect them using tasks, and then configure their behavior. Because we have already done the first two steps, it’s now time to configure each of the objects in the data flow. The first object to configure is the source object. In this object, you need to define the connection manager for the source database (create a new one if none exists) and the table or view name.

62. Source Configuration

After configuring the source object, click OK to close the editor and double-click the Conversion object. This will open its configuration screen where you can create expressions to transform the columns as needed. In this screen, select the columns from the source table or view you want to convert. In the conversion grid, select the destination data type and size.

63. Conversion Configuration

Now we want to create a new column and add it to the existing data flow columns. This will be a load date value which indicates when this record was inserted. To do so, double-click the Derived Column Transformation. When the editor opens, you need to give this column a name. In the derive column option, select Add a new column and, in the expression, use the GETDATE() function to get the current date and time.

64. Creating a new derived column

Last but not least, we need to configure the destination object. If you double-click the destination object, you will notice a big difference between it and the source object. You will see a new tab on the left called Mappings. In this tab, you are going to map the columns from the data flow which have been transformed or created to the columns in the destination table. However, the first step is configuring the connection manager.

65. Destination object connection manager

As you can see in the previous figure, there is a warning at the bottom of the editor that tells you that you need to map the columns from the data flow to the destination table. So let’s click the Mappings tab.

66. Mapping Columns

The goal in this editor screen is to be able to map between the available input columns and the available destination columns. Once you complete this task, you can run your package and the data will move from the source to the destination database.

Note: This is just an introductory example; it doesn’t have any error control or validations. It is meant for you to see how to start developing packages with data flows in its control flows.