Sometimes we get data in some bad forms. For example look at the below table:

The names and the amounts are comma separated in two different columns. Let’s imagine we need to get this data into a table like the one below:

Notice the column names are the names that were in the data. This can be accomplished with derived columns and conditional splits in an SSIS data flow.

Here is an example of a data flow that accomplishes this:

After the source the first component is the conditional split.

The conditional split sends the rows down different paths based on the number of commas in the data. This makes the derived columns afterwards much easier to handle. If not then we would have to nest a bunch of conditional statements in the derived columns.

The next steps are the derived columns. These will do different work based on the number of commas in the data.

With only one name in the data:

Two Names:

Three Names:

Four Names:

In these derived columns you are taking the items between the columns and separating them into their own columns. Here is a data viewer showing the data after the union all.

In the next step I decided to get rid of the nulls so the last derived column would be easier to write. If you want to leave in the null you can, but then the last derived columns will need to have a lot of ISNULL checks.

Now that the nulls are gone, and you have everything divided into individual columns, you can use one more derived column to arrange the data into the correct columns.

And now the mapping to the destination table should be easy. The ID column is mapped to ID and the names are mapped to their correct columns.

This method will work if you have a limited number of columns. The maintenance on this would be a headache if you have to add or remove names frequently. I would not suggest this method if you have frequent column changes.