Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

clip_image002

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.

clip_image004

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.

clip_image006

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

clip_image008

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

clip_image010

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

Name 1 Destination mapping.

clip_image012

Name 2 Destination mapping

clip_image014

Name 3 Destination mapping

clip_image016

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

clip_image018

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.


Comments

Leave a comment on the original post [mikedavissql.com, opens in a new window]

Loading comments...