In my previous post I begun to discuss the options available for getting data faster inside Dynamics CRM and discussed “Buy vs Build” option when it comes to data migration and also laid out some suggestions to make any built application better performant in data migration. Today, I want to discuss another alternative for data migration – SQL Server Integration Services (SSIS).
Source: MIchael J. Swart, Database Whisperer
SSIS is a tool that we can use to perform any Extract-Transform-Load (ETL) operations and the same can be used for loading data into Dynamics CRM. SSIS is one of the primary ETL tools used in Dynamics CRM projects for migrating data.
Here is a quick tip on SSIS to be tried while migrating large volume data into Dynamics CRM.
Use Balanced Data Distributor (BDD)
BDD takes advantage of concurrent processing capability of modern CPUs and distributes the incoming data into equal proportions of outgoing data, however many they are. If you have four outputs, roughly ¼ of the input rows will be routed to each output. Thus BDD introduces parallelism in the data flow of SSIS package and amps up use of multi-processor and multi-core servers.
Use of SSIS Balanced Data Distributor (BDD) requires a fair bit of understanding of hardware the package will be running, the performance of the data flow and nature of the data. BDD was designed to solve the bottlenecks of data migration. Generally, bottlenecks can happen in three places –
- Bottleneck at Source System. BDD is not going to help much in this case as BDD is out distributing input into multiple outputs.
- Bottleneck in the Transformation. BDD could help as it would parallelise the transformation logic.
- Bottleneck at Destination. BDD will be able to help in this case.