Monthly Archives: March 2017

SSIS Connectors Test Setup instructions

You can use the scripts and solution/project files below to set up the same data migration test that I used to compare KingswaySoft to CozyRoc in the Part 4 of the Data Migration with SSIS series of posts:


Local table setup – this script will create a table and add 5000 records to it

DECLARE @counter int;

IF OBJECT_ID(‘TestEntity’) IS NULL
CREATE TABLE TestEntity(
[Id] [int] NULL,
[CRMId] [uniqueidentifier] NULL,
[Name] [nvarchar](50) NULL
)

DELETE FROM TestEntity

SET @counter = 0
WHILE(@counter < 5000)
BEGIN
INSERT INTO TestEntity(Id, Name) VALUES (@counter, ‘Name’ + CAST(@counter AS NVARCHAR(10)))
SET @counter = @counter + 1
END


CRM Solution to create a test entity 

ssistest_1_0_0_0


 

SSIS Project

SSIS Test Project

 

 

Dynamics CRM: Data Migration with SSIS (Let’s put it all to test)

You can test SSIS components in many different ways – you can test performance, usability, user interface, advanced functionality, and so on. Some of those tests will depend on what exactly you are trying to achieve. Before we continue, let me clarify the goal of this particular testing exercise.

As you may recall, I mentioned that I used to work with KingswaySoft components on a number of Dynamics data migration projects, so I am not, really, trying to test whether KingswaySoft is up for the task because I know it is. However, I have not worked with CozyRoc up until now, yet it seems it should be able to provide more or less the same kind of functionality.

Therefore, here is what I have done:

I have also created an SSIS package which is supposed to do the following:

  • Copy data from the local table to Dynamics using “create” operation with batch processing
  • Load generated Dynamics guids back into the local table
  • Send the same data back to Dynamics using “upsert” operation with batch processing
  • Finally, the same package will delete all test data from Dynamics

So, it’s all been pre-set, and, as it turns out, it’s all working perfectly with either of the connectivity packs.

I won’t really make a recording of the test – you will find all the test setup instructions below in case you wanted to try it yourself.

However, I still wanted to show you how easy it is to replace one set of components with another set of components in the video below, so have a look:

If you wanted to try the same yourself, you will find most of the test setup instructions here.

It’s really up to you how to interpret all this, but, I think, there are at least a few takeaways:

  • KingswaySoft components and CozyRoc components are almost replaceable. Notice how it only took me about 5-6 minutes to convert a package that was using KingswaySoft components to a package that was using CozyRoc components. Sure it was not such a complicated package, but still
  • As with everything else, KingswaySoft is paying more attention to the visuals. There are dynamic hints, there are nice icons, etc
  • As far as Dynamics connectivity is concerned, it seems both products are offering about the same functionality

In the end of the day, choosing one product over another might be all about your personal preferences. Although, considering the licensing and pricing models of those two.. CozyRoc might just have the edge if your budget is tight.


This was the last post in the series. I’m hoping you have enjoyed the reading, and I will definitely appreciate your comments/feedback. So, get in touch and let me know what you think!

Dynamics CRM: Data Migration with SSIS (Connectors)

There are at least a few companies offering Dynamics connectors for SSIS, and it would be a little challenging to compare all of them. Personally, I used KingswaySoft connector on a few Dynamics data migration projects, and it never failed me. However, it is not the only one available, so you may ask why did I choose that one?

Come to think of it, there was no particular technical reason. However, KingswaySoft has always been doing a very good job marketing their products to the Dynamics community, and that’s, probably, why I ended up using it on my projects.

Still, just or the sake of this exercise, I wanted to see how something else fares against KingswaySoft, and it will be CozyRoc vs KingswaySoft today:

I have already mentioned that KingswaySoft is really good in promoting their product to the Dynamics community. It should not be too surprising, really. After all, Daniel Cai, who is the principal developer there, is also a Dynamics CRM MVP.

That alone gives that product a lot of credibility, at least as far as Dynamics connector is concerned.

But it does not stop there. If you look at the KingswaySoft’s web site, you’ll see it is all nice and clean:

KingswayHomePage

 

You will find an MVP program there, which seems to be very similar to the Microsoft’s MVP program. Actually, you will find a lot of Microsoft MVP-s on that list.

What it all does, it delivers a message: it’s a stable product, it’s backed by a strong community, and the company cares about its appearance. From my perspective, that translates into their desire and ability to stand behind the product if I ever run into a problem using those components in my data migration projects.

In that sense, CozyRoc is not, really, doing such a god job:

CozyRoc

Their web site is ok, but it’s not as polished at all. If you go to the “news” or “community” sections of the site, you will get that feeling that it’s been somewhat neglected.

Long story short, KingswaySoft comes through as a better established and more reliable company from the beginning.

Just keep something in mind, though. We are really talking about the companies developing add-ons for SSIS. That kind of software has limited complexity, so we may not even need a lot of support in using their products. In other words, even if KingswaySoft seems to have much better appearance, it does not mean, yet, that CozyRoc has lost the battle already.

But we need to start looking beyond the appearance, and what’s left, then, is technical capabilities and pricing.

Let’s start with pricing. Again, neither of those companies can afford to charge too much, so, in a way, there is almost nothing to compare. CozyRoc will cost you around $400 per year. KingswaySoft will cost you around $1300 per year. There is a difference, but, in both cases, those licensing fees will likely be well within the project budget, especially if you consider how much hours you will be saving for the developers/consultants.

Still, CozyRoc is a clear winner as far as pricing is concerned. For about one third of the price KingswaySoft will charge you, you will be getting two licenses instead of just one.

It starts to look better for CozyRoc, but let’s see what happens if we compare technical capabilities of both products. To make it simple and visual, I took a screenshot from CozyRoc web site, went to the KingswaySoft web site, and, then, highlighted those connectors which KingswaySoft is mentioning there(since, it seems, there is nothing KingswaySoft is offering that’s not available from CozyRoc):

CozyRocvsKingsway

Now this makes CozyRoc a clear winner on the technical side as well.

So, both pricing and technical parts of this comparison go to CozyRoc, while Kingsway is doing better on the marketing and community support side. I’m almost scared to admit it, but, it seems, I may have to change my preferred tool the next time I’m working on the data migration project. Although, before I do that, I wanted to try some sample data migration scenario and see how both products work in comparison, so let’s put it all to test!

 

 

Dynamics CRM: Data Migration with SSIS (overview)

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!

Dynamics CRM: Data Migration with SSIS (Introduction)

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:

enterpriseintegrationtools

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

forrestersipaas

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?