In regards to Data migration via SSIS to Dynamics or for instance any other system, just wanted to share some of the design aspects / practices which i believe should be considered
1.Package Structure – We must design our SSIS data migration packages in a very precise manner. In long run, it not only helps ensuring the data integrity between the two systems but helps us in maintenance and performance aspect of the system. We should consider factors like
- Designing intermediate databases – While moving data to the Dynamics or for instance any other system, there are several things we need to consider like lookups, option-sets etc. If we have the guid’s in database layer itself, we wont have to make calls to Dynamics to fetch there ID’s. Such transformations along with many others can be handled in that intermediate database.
- Maintaining hierarchy of packages – To maintain the data integrity, while migrating data, we need to maintain a particular order. There the packages must be designed that they execute in a sequential order.
2. Error handling – We should consider proper exception handling in our packages. Packages should be designed in a way to log errors related to data integrity, network connectivity etc. Proper mechanism to raise notifications to relevant parties should also be provisioned.
We should also consider the need to have stages or milestone for each of our package runs. These milestones could be different stages like Reading data from source, doing transformations in intermediate database etc. This ensures that if we need to rerun the package for the failed records, we do not need to rerun the entire package.
3. SSIS Package / Dynamics Settings – Mentioned below SSIS package settings can be changed to optimize the data flow
- a) DefaultBufferSize
- b) DefaultBufferMaxRows
- c) EngineThreads
Please refer to the msdn link for more information on these settings
If the target is Dynamics,
- We should use ExecuteMultipleRequest for the operations in Dynamics.
- For different entities, we should use combination of Batch Size and Multi-thread count and check the performance.
4. Further Readings – For SSIS , mentioned below msdn blog link is quite rich in content