Dynamics CRM: Data Migration with SSIS (Introduction)

By | March 28, 2017

Dynamics has an absolutely amazing set of SDK libraries which we can use to turn it into pretty much anything. It’s a bit of a curse, actually, since, every now and then, there is a Dynamics CRM project that involves so much development that you can’t help but think “well, would not it be easier to just develop a system from scratch instead”? Still, those SDK libraries are vital for any serious Dynamics implementation.

Funny enough, I got most of my initial experience with Dynamics SDK when I attempted to write a data migration tool, back at the times of Dynamics CRM 2011. Actually, if you never tried to do it yourself, you might not believe how much information those tools have to obtain from Dynamics as far as the metadata and actual data is concerned. Attribute types, option set values, primary keys, various types of requests, various types of errors.. it all has to be taken into account. Not surprisingly, I have not been able to create even a single commercial data migration product. Still, I had a few functional ones.. And here is what I learned:

  • With the proper application of development efforts, you can do almost anything with Dynamics SDK-s
  • Quite often, it actually takes a lot of time to do what you want to do
  • If you wanted to learn the SDK, you might consider creating your own data migration tool
  • But, most importantly, there are commercial tools which you can use for the data migration – all things considered, it is usually much more cost-efficient to purchase a license for one of those tools rather than to create your own. Once you have that, you may still want to develop a few additional tools just to cover the specific needs of you particular data migration project, but, trust me, that’s the extent of what you really want to develop

Come to think of it, that last one makes sense. Unless you are in the business of building software products, and, even then, unless you are in the business of building data migration tools, there is absolutely no reason to spend money on building yet another tool. You would not build a car yourself, right? You would rather buy it instead – that’s probably more expensive, in the end, but, at least, you’ll get a car quickly . And yes, I know people who would rather build a car.. it’s what they do as a hobby, though.

So, then, what are the tools we can use when we have a Dynamics data migration project at hands?

Let’s think of it a bit more first, though.. What exactly are we looking for? Since we are talking about the data migration, the purpose of that project is, usually, to move data that exists somewhere else to Dynamics. It might also be the other way around, of course, if we ever need to migrate from Dynamics to something else. Besides, there are can be mixed scenarios, when we need to move data back and forth between Dynamics and other systems; although, that’s probably more of an integration project.

Either way, what we are looking for is a tool that meets some or all of these requirements:

  • We need a tool that knows how to get data to/from Dynamics
  • We need a tool that knows how to get data to/from other data sources (SQL database? Oracle data warehouse? Maybe SalesForce? Etc..)
  • We need a tool that meets various enterprise standards in terms of support, security, resources availability, etc
  • We probably need this tool to be easily configurable. Otherwise, we might, as well, just hire a developer to create a custom application which would move the data around
  • Any additional perks would definitely be appreciated (ability to run scheduled jobs, for example)

This list can go on, but, the point is that not every tool will fit those requirements. Besides, there is a question of whether we need a tool that works purely in the cloud or if we actually need a tool that must be installed on-premise.

With all that said, there are quite a few tools on the market. However, traditionally there are two which every Dynamics consultant heard about:

  • Scribe
  • SSIS

It is worth mentioning that Informatica provides CRM data connector as well, although, since I have not worked with Informatica, there is not a lot I can say there.

Scribe is very popular, and that’s exactly why I did want to discuss another option here, since it does exist. Still, if you are new to this, keep in mind that, unlike SSIS, Scribe can run purely as a cloud service. SSIS, on the other hand, is a completely on-premise solution. If that does not sound like a big problem, and, also, if you have a SQL server license already(in which case SSIS is free), SSIS may end up being a more cost-effective solution exactly because of the licensing.

To wrap up this introduction and to show you how each tool measures against the others on the market, let’s have a look at the Gartner Magic Quadrant for the Data Integration Tools:


Do you see Scribe there? Well, neither do I.. However, have a look at the other report for the integration as a service tools:


Scribe is there, but is Microsoft mentioned there? Nope..

The difference is that the second report is looking at the integration as a service type of tools, whereas the first one is all about the enterprise data integration tools. As I mentioned, Scribe does have that ability to work purely in the cloud; however, it is not always an advantage. For example, in the secure environments where classified documents are involved, and just about any government department would be like that, moving data to the cloud might not be an option at all. In which case there is still an on-premise version of Scribe; however, that becomes a much more interesting competition then.

So, how does Dynamics data migration work with SSIS?

Leave a Reply

Your email address will not be published. Required fields are marked *