Dynamics CRM: Data Migration with SSIS (overview)

By | March 29, 2017

There is something about SSIS that may not be quite clear when you first look at it from the Dynamics data migration perspective. You cannot really use out-of-the-box version to create data migration packages which will work with Dynamics. Actually, that might be a bit too much to say, but, if you are not a developer who knows how to utilize Dynamics CRM SDK and how to write SSIS components, then that’s exactly how it is. You can write custom code and utilize it in SSIS, of course, but you have to learn how to do it first.

Which is probably not your goal at all.

Luckily, there is a solution to that. SSIS can be extended by adding custom connection managers and custom data sources:

ssis_overview1

(You can find original version of this diagram here: https://technet.microsoft.com/en-us/library/bb522498(v=sql.105).aspx)

There are a few companies on the market offering SSIS connectivity packs for Dynamics – here are just some examples:

And this is where you may need to stop and think about it.

If you look at the products like Scribe or Informatica, they do provide there own connectors for Dynamics CRM. On the high level, their architecture is very similar – they have some sort of a core engine, and, then, they have various data source connectors. You can even purchase licenses for those connectors separately depending on which connectors you really need. However, from the sales and support standpoint, you will be dealing with a single company.

SSIS has, essentially, the same architecture, but it does not offer that many out-of-the-box connectors. Instead, there are other companies providing those. So, from the sales and support standpoint, you now have to deal with different companies and that can be a bit of a complication. For example, even though Microsoft’s credibility won’t be questioned, will you still need to approve smaller software vendors before you acquire any software licenses from them? Even if not, you will probably need to do some sort of internal risk assessment first, and that’s where you may realize that relying on a relatively small company for your multi-million project data migration needs might not be what you want to do.

In other words, don’t be confused by the Gartner Quadrant mentioning Microsoft SSIS – as far as you are concerned, there is a missing piece of functionality there which you need to purchase somewhere else.

The alternatives are:

  • Create your own data sources/data connections for SSIS (in other words, hire a developer)
  • Switch to Scribe/Informatica/Other big ones

Of course, at that point it all comes down to your budget – both of those options are, usually, more expensive than utilizing a”free” SSIS license (assuming you have a SQL Server license already) and a Dynamics connector license from one of the third-party companies. Their licenses are significantly cheaper, and that actually makes perfect sense since they only need to develop and support a relatively small piece of functionality required for the data migration projects. Microsoft is still responsible for all the heavy-lifting and orchestration.

Last but not least, since we are talking about data migration rather than about the ongoing data integration, you may not really need a SQL server license to use SSIS. In order to develop SSIS packages, you will be using Sql Server Data Tools (SSDT), and they are, actually, free. Of course you cannot use SSDT to schedule those migration/integration jobs, but that would be more of a data integration scenario anyway, and, quite often, the goal is not to provide ongoing integration, but, rather, to implement a one-time data migration.

And it’s probably time to take a close look at some of the companies offering CRM connectors for SSIS, so let’s have a look!

Leave a Reply

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