Explode rows in SSIS

A request I receive more and more frequent from my customers, is the ability to create multiple rows based on a variable value in a field in CRM (for example, an integer or a calculation between several fields). There are several ways to accomplish this, but I’m using one in SSIS that requires zero code, and I would like to demonstrate one of its applications here. The method does require access to the SQL database, as one of the main components is an Execute SQL Task.

This is a four step process:

1. Execute SQL Task
2. Foreach Loop container
3. For Loop container
4. Data Flow Task

Step 1 – Execute SQL Task:

This is where you select which data you want to explode into several rows. This can be any data you want, as long as you can collect it in a SQL select statement.

SELECT recordId, name, amount
FROM recordBase
WHERE Status = '0'

With this statement, I am selecting 3 columns: the Guid, name and amount – in that specific order, and the order is important, when we are mapping the data to variables. We now know which data we want, so we can create the variables we need to hold them. For this dataset, we need 5 variables. 1 object to hold the dataset, 3 variables for each of the columns and 1 counter variable, which we are going to use, when counting the records we are creating.

In the SQL Task, we need to map our dataset to the object, and for that, we need to set the resultset to Full result set.

and then we are going to map the full resultset into our newly created object.

Step 2 – Foreach Loop Container:

This is the tool we need to map the resultset to different variables, using an Foreach ADO Enumerator. The ADO Enumerator allows us to select an variable object, and select the rows within it.

After selecting th object we are going to use, the variables can be mapped to different indexes in the object. This is where the order of the SQL statement is important, as the first column is index 0, the second is index 1, the third is 2 etc etc…

Basically, this means for each rows which enters this Foreach Container, the Guid, name and Amount is being mapped to variables for us to use as we please, while we are still inside the Foreach Loop container. What we are doing here though, requires us to insert another container – the For Loop Container.

Step 3 – For Loop Container:

As we want to work with the variables we created, the For Loop container needs to be placed inside the Foreach loop container. Inside the container, we need to set the properties for creating multiple rows:

The initExpression is @counter=0. This means, the Counter variable starts at 0 when a new record hits the container. The EvalExpression is when the loop needs to stop. In this case, I want it to stop when the Counter is no longer smaller than the RowAmount variable. The AssignExpression is where I define, what happens each time the loop is running. @Counter=@Counter+1 means I add 1 to the Counter each time it runs. In the case where the RowAmount is 3, it runs 3 times, before the Counter is no longer smaller than the amount.

Now that we have defined a method for how many records needs to be created, we need to actually create the records.

Step 4 – Data Flow Task:

In here, we want 4 tools. A source, derived column, Conditional Split and a destination. Ideally the source should be the same as the one you used to create the variables with in the first SQL task, but it can differ from case to case. In my case, I use an OLE DB Source with the same SQL statement.

I then use a derived Column, as this is where I create columns with the variables.

The variables are now mapped, and we can use them in our destination, but first, we need to make sure, we create the rows to the correct record. We are have more records in the original query, the Loop container can get confused as to which belongs to which, so we need to add a conditional split. I am matching the guid from the OLE DB source and the guid I have in my variable. This makes sure, I create the correct number of rows to the correct record

The only thing missing, it to map the data to the destination. This can be whatever destination you want. CRM, ERP, SQL, Flatfile, excel etc etc, but in this example, I am going with an excel destination

Running this job will turn this:

into this: