Iterating and Processing Records in Dynamics CRM using SSIS

Introduction:

SSIS provides us a seamless way for data integration. Data can be easily extracted, transformed and merged from source to different destination. In one of our recent post we explained about Exporting Data from Dynamics CRM using SSIS.

Let us see one such scenario in SSIS where we need to get data from one source which is a collection, perform some transformation and provide it as input to other destination.

Here we tried to provide a walkthrough to help Iterating and Processing Records using SSIS.

Walkthrough:

Let’s start with creating package. Open Visual Studio and go to -> File -> New -> Project.  

There from Business Intelligence tab select -> Integration Service Project.

Note: You need to have BIDS installed for you to get Business Intelligence Services tab.

When you click on OK you will get a solution created with a package file added.

Let’s take one Data Flow Task from the SSIS tools and drag drop on our package.

Double click on the Data Flow Task and you will enter into Data Flow tab. Add the source control from where data will be fetched. Here in my case we are using Kingswaysoft Dynamic CRM Source from where data will be fetched.

Now add a RecordSet destination control from tools to package as follows:

Right click on Recordset Destination and click Edit.

Next you need to create a package level variable which is of type object which will hold all the records fetched from the source. Right click anywhere on Control Flow tab and select variables as shown below:

A variable tab will be opened. Create a new variable and select its type as object. This will be a package level variable. Since here in this example I am dealing with Account entity which I want to process so the variable I will create is AccountRecords.

Now go to RecordSet Destination and right click on it to edit it. A advance editor tab opens as follows:

Note that here in VaribableName property we set the variable which we created for holding records. Next go to the Input Columns tab and select the columns which you want to include as follows:

Next in Input and Output properties tab you can see the columns which you selected.

Click Ok and the record collection fetched from the source will be stored in the package object type variable.

Next we need to add a Foreach loop container. Here we can iterate through all the records and perform further processing as follows:

Right click on Foreach loop container and Click on Edit.

The Foreach loop editor opens. Now go to Collection tab and select Foreach ADO Enumerator as the Enumerator and select the ADO object source variable as the package level variable which was set in the Recordset as follows:

Next in Variables Mapping tab we need to specify the variables which will map to the columns from the ADO object source variable as follows:

Here AccountNo and AccountName are variables which are of type string which are created in the same way we created AccountRecords variable. Here you need to specify the Index of the variable which you get in the collection result.

Now using these variable values you can perform various processing which you want. For example we can check if the AccountNo starts with the specified characters and if yes then write those AccountNo and AccountName to an output file as follows:

Add a Script Task and Data Flow Task in the loop. Right click on the forward processing success arrow which we have connected from Script Task to Data Flow Task and specify the expression based on which we want to move ahead in processing.Now when each record will loop through the container it will be checked for each record if the AccountNo starts with “ACC0B”. If yes then it will move ahead for further processing or else next record will be processed. In further processing in Data Flow Task you can process this data the way you want by writing it to another destination which can be anything (like another CRM destination like the source or text file etc).

Conclusion:

In this way you can loop through records and process them based on the conditions as per your business needs.

Hope this helps!

Do more with your Dynamics CRM..Try QuickBooks and Dynamics CRM Integration – InoLink.