Well this time I have another interesting problem to share that I recently solved with another client integration project moving data into CRM 2011.
This project called for migrating an old CRM 4.0 Database to a new CRM 2011 instance. Naturally I fired up Scribe Insight 7.5 to manage the heavy lifting for my data work, but this was not going to be your everyday straight forward data migration.
Here are some of the requirements/nuances of the job…
The 4.0 CRM system is no longer up and running and there is no available hardware to fire up a 4.0 system that I can connect the old DB to. This means I will need to connect Scribe directly to the database using an ODBC connection and I can’t use the native CRM 4.0 Adapter that Scribe provides. The CRM 2011 system is an on premise implementation and I can use the CRM 2011 Adapter for my target connection.
I then need to copy all of the email activities along with the email body, to/from, cc, regarding, etc. over to the CRM 2011 system. This is a bit tricky but with some good lookups and patience, Scribe makes it a breeze. There’s been quite a bit of how to do this documented elsewhere on the web so I will not go into those details. If however, someone would like to know how I did it, let me know and if I get enough folks who would like those details I’ll create a new post to cover the details.
So far so good.
For my source connection, again as I mentioned we are using an ODBC data source to connect to our “Org_MSCRM” SQL DB from our 4.0 system. This connection is then specified as our DTS source connection and we go through our views and select our filtered.email view. (Using filtered views is recommended by Microsoft as the supported way to connect directly to table data in CRM). ** If you need to limit the data returned, for instance rather than grabbing ALL emails, you may only want emails from the last few years, you can use a SQL query as your source query to return only the records meeting your date created/modified rules. On our target side since we are using the Scribe CRM 2011 Adapter, we simply create an insert step on the email object.
OK, so now we are ready to map our fields between source and target and fire up the test window to check our work. Now the way the emails work in CRM is that from a table point of view, the actual HTML markup for the email body is stored in a large CHAR field in the table called “Description”. So this is where you will see the body of your email. But in this case, as shown in the below screenshot, our “Description” field is empty!
So what happened? Where is our data?
Well it turns out that when using an ODBC connection with Scribe, you need to be able to tell Scribe that it should look for specially encoded characters known as “Unicode” characters. There is a setting for this, that is NOT enabled by default. It can be found in the properties of your data connection.
See the Screenshot below for the check box you need to check off.
Simply go to your connections manager in the workbench, select your source connection and double-click it or open it. The setting window appears as above. Make sure to check off the “Support Unicode Data” option. Save it and then return to your workbench and open you test window again.
Happy migrating! If you have any questions please let me know!