CRM, SSIS & Integrations ...configuring CRM as a non-coder
  • Home
  • Categories
    • Visual Studio
    • KingswaySoft
    • SSIS
    • JavaScript
    • CRM
    • SQL
    • ERP
    • Azure
    • How-to
    • Clubtimiser A/S
    • Office
    • Personal
  • About me
  • Privacy Policy
CRM, SSIS & Integrations
  • Home
  • Categories
    • Visual Studio
    • KingswaySoft
    • SSIS
    • JavaScript
    • CRM
    • SQL
    • ERP
    • Azure
    • How-to
    • Clubtimiser A/S
    • Office
    • Personal
  • About me
  • Privacy Policy
  • Home
  • SSIS
  • Explode rows in SSIS

Explode rows in SSIS

June 7, 2016 Leave a Comment Written by Thomas

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.

explode_01

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.

explode_04

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.

explode_02

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

explode_03

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.

explode_05

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…

explode_06

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:

explode_07

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.

explode_12

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

explode_10

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

explode_11

Running this job will turn this:

explode_13

into this:

explode_14

 

SSIS
Data, Excel, How To, SQL, SSIS
Using KingswaySoft’s auditlog in CRM
Possible CRM bug: Business rules overrule Read-only records

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

The author

KingswaySoft MVP & Microsoft certified professional

My name is Thomas and I am a consultant at a company called netcompany A/S in Denmark. My primary focus is working with SSIS and KingswaySoft, where I perform integrations and datatranformations.

Click for more info

Recent Posts

  • Difference between two dates (SQL, SSIS and JavaScript)
  • Easy way to fetch row count for all tables in a SQL database
  • Create Application user in Azure for use in Dynamics 365
  • SSIS Basics: Merge join
  • Open dashboards in a new window

Useful information

  • Installation guide to KingswaySoft
  • Software download list
  • CRM Object Type Codes
  • CRM State & Status codes
  • Script Repository

Archives

  • May 2020 (1)
  • February 2020 (1)
  • January 2020 (2)
  • August 2019 (1)
  • May 2019 (1)
  • February 2019 (2)
  • January 2019 (2)
  • November 2018 (1)
  • September 2018 (1)
  • August 2018 (3)
  • May 2018 (4)
  • December 2017 (2)
  • August 2017 (1)
  • February 2017 (1)
  • November 2016 (4)
  • October 2016 (1)
  • September 2016 (2)
  • August 2016 (5)
  • June 2016 (4)
  • May 2016 (3)

Useful blogs/links

Henrik Jensen, my good friend and colleague, has a blog regarding EVERYTHING Dynamics crm. He's got 50+ certifications, and he is a force to be reckoned with. Link to his blog (danish): crmblog.dk

KingswaySoft have developed an outstanding piece of software for datamanagement to and from CRM, and I can recommend this toolkit for nearly every situation. Link to their website: kingswaysoft.com

Tag Cloud

Azure Bug CRM 2015 CRM 2016 CRM Online Data Dynamics365 ERP Excel How To JavaScript KingswaySoft Log News Office Outlook Personal Solution SQL SSIS Visual Studio

Categories

  • Azure (2)
  • Clubtimiser A/S (1)
  • CRM (30)
  • ERP (2)
  • How-to (14)
  • JavaScript (3)
  • Office (3)
  • Personal (4)
  • SQL (2)
  • Visual Studio (23)
    • SSIS (23)
      • KingswaySoft (14)

Disclaimer

The information given on the website is tested on a mixture of live production environments and test environments, and therefore I cannot guarantee my information will work for you, if you attempt to follow my steps.
If you experience problems, please feel free to contact me.

@June 7th 2016