Data Migration – how do you usually do it?

In the last few years, I worked on a few Dynamics projects that involved data migration, and, somehow, most of the times I would settle on the approach to the data migration which I tried to illustrate on the diagram below:

The main reason I usually do it this way is that, when I have that database in the middle, I can do whatever I need using SQL queries and stored procedures, and, once I have transformed all the data, I can simply push it to Dynamics.

For example, here is a sample scenario:

• I may have contacts in the DataWarehouse
• I may have contacts in the portal database
• I may have contacts in Dynamics

How would I create a “merged” version of those contacts in Dynamics?

One way to do it would be to use the integration tool to transform that data on the fly, to lookup destination records in Dynamics, to apply conditions and so on. Quite frankly, I think I don’t feel that comfortable with the integration tools to do all that within such tools. Besides, it’s almost like I’m loosing some level of control when doing it that way since I can’t really see a snapshot of the data that’s being sent to Dynamics before it’s sent to Dynamics.

Another way to do it would be to follow the process depicted above. In that case, I would, first, move all the data to the local database. It would not necessarily have to be a separate contact table per data source – it might be a single contact table with a bunch of extra columns. Or it might be a stored procedure that would do some kind of smart lookup. Or it might be just a SQL query that would do a relatively complicated update. In any case, the end result of that transformation process is a table that I can simply push to Dynamics. Instead of using text lookups, I can push actual guid-s. Instead of having to find option set values, I can simply push those values to Dynamics. In other words, that last step (from local DB to Dynamics) does not involve any data transformations (well, maybe it does involve some.. in the form of a SQL view or query).

One strange implication of this approach is that I don’t, really, need most of the advanced features which would be available in some Dynamics connectors and would not be available in others. Because, after all, all I really need is the ability to get data from Dynamics and to push it back, field-to-field. Granted, I need that “local” database somewhere, but, usually, that’s not so much of a problem.

This is why, for example, when I wrote the post below back in March, CozyRoc adapter for SSIS turned out to be almost my favorite when compared to Kingsway (if you don’t need those advanced features, the next thing you want to compare is pricing, and CozyRoc ends up being a winner):

http://www.itaintboring.com/dynamics-crm/dynamics-crm-data-migration-with-ssis-connectors/

However, since I was getting some questions about that comparison, I realized that I had to explain why it was done that way. There is no doubt it all depends on the specific approach to the data migration. Yes, I don’t use some of the advanced features of the KingswaySoft, but, to be fair, Kingsway does offer functionality which is not available from CozyRoc. For example:

• Text Lookup
• Local cache
• Ability to run workflows

But, then, most of the times I would not be using it..

Having said that, I’m wondering what’s your experience with the data migration in general and with those Dynamics connectors in particular? How would you normally do it? Which connector would you prefer and why? Let me know.