Saratoga Technologies Microsoft CRM Blog

Well…. It was another great convergence. One thing that always happens when i attend the event is certain. I am always insipred by what i see, and from what others have achieved with CRM and GP and i know the sky is the limit for the platforms.

The cloud is definately alive and here and has become a force in the marketplace. How vars adapt to the changing market will be interesting in the next 5 years, but this will allow so much value to all businesses who get involved with these solutions.

It was good to see the release of Dynamics GP 2010, and participate in the upcoming Sharepoint 2010 releases. There were some great advanced troubleshooting CRM and GP sessions put on by the premier support engineers team as well.

Great things to come in 2010 and looking forward to Atlanta 2011 as well.

I am wrapping up a integration project and thought i would post some valuable information in case anyone else out there is also looking to integrate some older MySql applications with Scribe and Microsoft CRM 4. I learned a few pointers during the way and i hope that it helps.

I  faced a few obstacles with the Scribe in a particluar MySQL to CRM Integration.

1.)    The first obstacle was that there is no native integration adapter with the community edition of mysql. Most websites and other applications use the community edition because it has been free and is still free (currently) for use. Since Oracle has purchased MySql it may not be so for much longer but that is only my personal opinion…

Scribe promotes that they integrate natively with MySql and they do…. But…. the catch is not actually with the free community edition of the software! If you want to use scribe and the customer doesn’t mind paying the annual license fee for mysql enterprise ($900) then scribe will work natively with that version and the oledb adapter that comes in scribe insight.

Otherwise you will get a message that it doesn’t work with the community edition. ughh…. Actually really what is the difference in connecting to the database itself? Anyways…

2.)    The next obstacle faced was that using the odbc drivers would time out  your integrations in the Scribe Workbench.  Ughh… You only have to use the odbc drivers because you cannot connect natively with Scribe, but either way another workaround.

A work around for this is to create your statements ahead of time, and not actually “build” them in the scribe workbench (as intended). Otherwise the odbc driver connections would time out. You can quickly paste them into the workbench as a source and click next to define your source as quickly as possible.

3.)     The third and definitely largest problem was that the scribe trigger and shadow tables generation script don’t exist for MySQL! Ok, so with Sql Server Scribe provides native scribe shadow and trigger scripts that allow you to natively use Scribe on databases and tables with large datasets without any additional coding needed.

You must understand that this is not so with MySQL, “Triple ugh”.  This means that the idea of “real-time” integration with scribe and a MySQL data source isn’t even possible “out of the box” with the software without some additional application changes, but we can find a solution to that also.

There are (2) ways that I found around this issue.

a.)    Go into the application that you are wanting to integrate with and attach a flag field to every db table, and update your entire application to additionally write lines into the a shadow table for processing from scribe. (definitely not an option with the site we were integrating)

b.)    The second and the option we chose was to create our own trigger scripts and customized shadow tables for each MySql table that needs the ability for “real time” integration. After looking at the application there were only a few tables that actually needed “real time” capability because the rest are not updated as often.

We created an exact copy of a table’s structure (without primary keys of course), and created triggers for Mysql to dump into those tables anytime that the table had a new record added, or a record updated. The record then ends up in the shadow table with the record itself an additional timestamp, scribestatus, and an update type (insert or update) appended to it.

This allowed complete integration without having to dig through the complete application and change all pages in it.

Using option (b) we are also able to control the status of the record in question to be updated, but this actually gave way to another problem, but at the same time it gave us a resolution as well.

 The problem was that processing the DTS records will take a certain amount of time. During this time we need to ensure that only specific records are inserted/updated, and that the rest that are populating in the tables are left untouched until the integration completes and is re-ran in the next DTS cycle.

This is where the Pre-and Post integration commands come in handy. Before the WorkBench DTS runs we can do a post mysql command to change our status field in the shadow table to something meaningful. In my case I changed it to the number “2”. 2- means “being processed by Scribe”.

The DTS Job that runs only looks for fields in my shadow table with the value of 2 for source records. From there it does all the processing needed, and logs any errors for the integration job.

After the DTS processing is complete I run the Post integration job to cleanup any records in the shadow table with a value of 2. Leaving the new and untouched inserts and updates in the table with a value of 1, ready for the next scribe DTS cycle. Perfect!!!

This way I can schedule the integration to run every few seconds 10-15 and will have a very small number of records to process (based on the tables current usage), and this will give the ability for a real time integration for the application that is integrated.

There are lots more to this, but i will cut it short for now, and i just wanted to say that Scribe is a great tool for integration but sometimes you have to get a little creative with it and unsupported data types.

If you have any questions or comments feel free to let me know. Thanks!

Here is a GREAT list of CRM 4 resources that i found from Wim Coorevits Blog – http://wimcoorevits.blogspot.com/

Microsoft Dynamics CRM 4.0 User’s Guide (Document – Microsoft)