Sometimes we come across a situation where there are millions of records that need to be transferred from SQL to Dynamics CRM. When we use SSIS to migrate such data there are several methods in it through which we can achieve this. But migrating so many records is not easy when it comes for speeding up. By applying some mathematical calculations in SQL query along with SSIS controls, we can achieve this. Let’s take an example where we want to transfer some millions of contact records from SQL to Dynamics CRM Contact entity. For this, we need to have a unique integer (index sort of field in SQL).
We will start with dataflow task. Add data flow task as shown in below figure–
Figure 1:
Double click dataflow task and go to data flow tab. Here I have taken OLE DB Source to fetch data from SQL. But since our data is in millions we need to build a mechanism where we can run in parallel to fetch data. We can do that as follows.
Figure 2:
Here you can see that we have taken 3 OLEDB data sources, which will query and fetch data from SQL “tblPeoplesData” table and the query for the 3 sources will go like this on the index column “SerialNo” as mentioned above.
Get data from PeoplesData 0 – select * from dbo. tblPeoplesData WHERE ([SerialNo] % 3) = 0
Get data from PeoplesData 1 – select * from dbo. tblPeoplesData WHERE ([SerialNo] % 3) = 1
Get data from PeoplesData 2 – select * from dbo. tblPeoplesData WHERE ([SerialNo] % 3) = 2
Figure 3:
So we did a modulo query on index column and fetched the data simultaneously. In this way we can speed up the data fetched from SQL when we have huge amount of data to be migrated. Here I have used only 3 OLEDB data sources. You can increase the count and query subsequently. But don’t forget that too many simultaneous fetch to same data source can slow it down. Then we gather all the data using Union All control as you can see in the Figure 2 where the data fetched simultaneously will be collated together. If needed, you can add Sort control and sort the collated data from Union All control. Then you can add script component if needed to process the data and send to CRM Contact entity (here we have used Kingswaysoft Dynamic CRM Destination control). Thus we can transfer data in much lesser time than what actually is needed to transfer data from SQL to Dynamics CRM.
How integrating QuickBooks with Dynamics CRM Transforms Your Business? Try InoLink today!