Blog Post

SSIS Records on the Same Row – kind of like pivot


If you have two or more records on the same row, and need to write each record on its own row in a destination, you have two choices. You can do this in series or parallel in a single data flow in SSIS. Here is the input table I am using for my example.


Notice I have three names on one row. I need these to be inserted into a table with a first name and last name column only. So all three first name fields need to be mapped to the only first name columns on the destination and the same is true for the last name column.

The first method I will show is using a multicast and a union all as seen below.


The multicast clones the data into three data flow line. In the union all we will now select the first name and last name columns to union together. We are going to stack them as shown and delete the unused columns. This gives us only two columns out of the union all.


This makes the mapping in the destination easy. It is simply two columns to two columns.


The second method is to spilt the data up and write it to the database in parallel. Here is that data flow.


This will write the data to the data base for each customer in a separate destination.

Name 1 Destination mapping.


Name 2 Destination mapping


Name 3 Destination mapping


Here is the data on the destination table after the load. Notice the names are all on individual rows.


In terms of performance, the parallel load works about twice as fast. This is in part due to the union all being a partially blocking transform and the parallel is writing three fields at once. This is a huge performance hit. If the parallel load time is 5 minutes, then the series load time would be about 10 minutes. This may seem small, but scalability should always be considered.


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating