Scribe Insight and Microsoft CRM 2011

In my last post I shared my first impressions of Scribe Software’s Scribe Online product. In this post I will have a similar look at their other product: Scribe Insight

Scribe and their Scribe Insight product have been around for 17 odd years, providing a data migration and integration toolkit for a range of CRM applications.  The product has a long history with Microsoft CRM too and is used by a vast number of partners and customers globally. 

They offer an interesting product that offers an alternative to custom .Net scripting, fitting somewhere in between CRM’s Data Import Wizard and the likes of SSIS, BizTalk and TIBCO.  They have licensing options aimed at data migration scenarios where you buy a 60-day license at a discounted price.  And they have your normal one-off purchase + annual maintenance license options as well.

You can try the product for free on a 30 day trial, with a 100 record limit so its not difficult to get your hands dirty if you want to.   Here’s my experience doing just that…

 


 

The installation experience was much like any other product.  You have the Scribe Insight product to install and then each Adaptor that you wish to use.  I went ahead and installed the Microsoft CRM 2011 Adaptor.

They have adaptors for a range of CRM and ERP products and integration approaches for other products where an adaptor is not required (such as SAP):

image

(For those wondering how Scribe enables integration with SAP check out this video)

Its these Adaptors that make Scribe attractive.  They take away the need to learn the specifics of a system’s database schema or APIs, you just have to map your data to the fields the Adaptor surfaces and it takes care of uploading your data and takes responsibility for maintaining the integrity of the database.

 

Scribe Data Migration from a Staging DB into Microsoft CRM

Ok, let’s work through a data migration scenario.  I have a source system that contains Contact and Account records and I want to load those into my lovely new Microsoft CRM system.  Each of my Contacts is parented by an Account and each Account also has a Primary Contact.  So, I’m going to have give my data migration sequencing a little thought.

I get the data out of my source system and load it into a pair of staging tables in SQL Server.

Staging Database: 

image

Contacts:

image

Organisations (Accounts):

image

Now, if my source system was Salesforce.com or Goldmine or something like that I could have used one of Scribe’s adaptors and skipped the need to extract to a staging db.  But for this exercise I will just use SQL as my source.

I launch Scribe Workbench – this is Scribe Insight’s console for configuring migration and integration jobs.  First thing I need to do is connect to my source data, so I click the Configure Source button:

image

Here I can choose the CRM adaptor I installed or a ODBC, text file or XML connection.  I choose ODBC and configure a connection string to my SQL database:

image

Next, I drill into that connection and chose the specific table I want as my source.  I am going to import my Account records first so I pick the corresponding staging table:

image

With the migration source defined I now move onto the destination system and the actions I want executed.  I click on Configure Steps and from a similar connection dialog I chose my destination system, in this case Microsoft Dynamics CRM 2011:

image

I am prompted for my CRM connection details:

Note: CRM On-Premise, Online and IFD are all supported

image

Now at this point some advanced settings become accessible.  I’ll give you a quick look at them as in some scenarios they will be quite important:

image

image

Next, I select my destination entity:

 image

And the action I require:

image

Next I need to map the individual fields.  I highlight my first source field, and then the matching destination field and then I click the Data Link button:

image

I repeat for each field, except for the Primary Contact field.  I will need to come back and update the field later once I have migrated the Contacts.

Now, one of my fields requires a bit of transformation on the way through.  My Customer Ranking field is a pick list in CRM with options ‘Gold’, ‘Silver’ and ‘Bronze’ and with underlying database values of 1, 2 and 3.  But back in my source system these rankings are stored as G, S and B.  My preference in a scenario like this would be to perform this transformation in the staging database with SQL scripts rather than mucking around with transformation in a migration tool.  But you can’t always do this (e.g. when migrating directly from a source system) so let’s see how Scribe addresses on-the-fly transformations.

Scribe’s answer is Cross Reference Keys.  From the toolbar I click the Cross Reference Keys button and then inform Scribe where it has to look to find the mapping between my source and destination values.  When you install Scribe it creates a SQL database for itself called SCRIBEINTERNAL and within that db there is a table you can use to house cross reference maps.  I select that table (“KEYCROSSREFERENCE”) and tell Scribe to look for rows labelled “Ranking”:

image 

Once I understood this was Scribe’s approach I jumped over to SQL Mgmt Studio and populated that mapping table with the necessary mapping for my Ranking field:

image

There’s a bit more to the configuration but that gives you a feel for the process.

Let’s do a bit more transformation.  Let’s say we want to convert the Company Name to upper case on the way through.  For this we will use a Formula.  I select the Name field and then click the Formula button:

image

Note in the above screenshot the source “Name” field over on the left side of the screen has been assigned the reference “S2”.   This comes into play in the formula designer.  By default every mapping has a formula, as you will see in the screenshot below.  The formula simply says “this destination field should be populated by the source field known as S2”):

image

We can easily change this formula and Scribe offers a bunch of functions for the common transformations required.  I expand the “Text” function category, select the UPPER function and see the formula automatically updated for me:

image

In a similar manner we can apply a formula to the Phone number field to take only the first 6 characters:

image

Ok, that’s enough fluffing around, let’s see if this will work.  Scribe has a nice little Test feature you can use to preview the migration with.  I run that, and can see how Scribe will map a specific source record.  I can click Next and Previous buttons to scroll through my source records and make sure a variety of records all appear to be mapping as expected:

image

So far so good.  Let’s run the migration.  I click the Run button and very quickly Scribe reports the result of the migration:

image

And we have success!  Here’s the result in CRM:

image

image

Now that I have my Accounts successfully migrated into my CRM system I can now load my Contacts.  I save the Account migration definition (Scribe calls it a “DTS”) and then click the New button to start a new definition (you have to create a new DTS for each source entity/table). 

I go ahead and repeat most of the same steps I just went through for Accounts.  I then get to the point where I need to figure out how to handle the Customer ID (Parent Account) field.

Now my source system had its own unique IDs for Accounts and its this value that’s stored on each Contact in my source data to provide the link between each Contact and its parent Account.   CRM has its own unique key of course (the GUID) but to support this migration I made sure I loaded the source system’s unique key into CRM when I uploaded my Accounts.  I pushed that “Customer ID” field into the Account entity’s “Account Number” field (a spare field I had available in CRM). 

To be honest, I find this next bit fairly confusing.  I did get it to work though so I think it might just be bad terminology.  Let me try and explain.

The field we are trying to populate is the “Parent Customer” field in CRM.   We are going to derive the GUID required for this field via the “Customer ID” field in our staging db.  So step 1 is to create a Data Link between those 2 fields:

image

But a direct data link by itself won’t work as the source system has values like “1” and “2” in the “Customer ID” field and we need to put a GUID into the destination system’s “parentcustomerid” field.   If we look at the formula behind the mapping you will see the current formula is insufficient:

image

What we need to do is change this formula and have the destination field get its value from a “Cross Reference Key” rather than directly from the source field.   That means we need to create a Cross Reference Key that will translate the source system’s “Customer ID” into a CRM Account GUID.

Now rather than referring to a cross reference table in Scribe’s Internal database like we did for the pick list mapping – we instead point Scribe at CRM for the necessary mapping.   The mapping is there for us in the Account table: the Account records I imported each have a GUID and they also have the “Customer ID” unique key from the source system sitting in the Account Number field. 

So what I need to define is something like this:

Mapping table: the CRM “account” table

Source system to Mapping table link:  “Customer ID” –> “account.accountnumber”

Mapping table to Destination system link:   “account.accountid” –> “contact.parentcustomerid”

Alias for this mapping: ParentAccountID

And this is how you fill in the Cross Reference Keys form to achieve the above:

image

Last step is to go and correct the data map formula.  In the formula editor I click on the “Show User Variables” button and then select the Cross Reference Key alias I just defined:

image

End result is the formula below:

image

Having sorted that I run the migration and yay – another success!:

image

image

We’re 2 thirds of the way there.  We’ve loaded the Accounts and Contact and linked each Contact to its parent Account.  Now, we need to go back and populate the Primary Contact field on the Account records that we omitted earlier.  We had to omit it because the Contacts didn’t exist in CRM at the point in time we were creating the Accounts.  It was a crazy chicken and egg Dr Who Timecop John Connor paradox thing.

So, we need to re-run the Account records from our source system back through Scribe and run an update.

To do this, I need to figure out how to get Scribe to do an update rather than an insert.  First step towards achieving that is to chose “Update” as the action required.  I do this back when I first select CRM as the destination system:

image

Then I need to tell Scribe how to match each source record to its corresponding destination record.  I select the “Customer ID” field in the source system and the “accountnumber” field in the destination system and click the Lookup Link button:

image

This tells Scribe that wherever these 2 fields match it should do an update. 

Next, we create a data link between the “Primary Contact” field in the source system and the “primarycontactid” field in the destination system.  This link will of course require transformation so another Cross Reference Key will be required.

So what I need to define is something like this:

Mapping table: the CRM “contact” table

Source system to Mapping table link: “Primary Contact” –> “contact.new_externalcontactref”

Mapping table to Destination system link: “contact.contactid” –> “account.primarycontactid”

Alias for this mapping: ContactID

Here’s how that looks:

image

Last step, just like before, is to edit the formula on the data map to refer to this mapping:

image

And if we run that, yeap, it’s success again:

image

image

Now that we have our jobs working individually lets string them together to give us just the one data migration task to execute.  One approach to this is to make use of Scribe’s command line capabilities.  Here’s a simple batch file that launches each of my 3 DTS jobs in sequence.  The “/RS” switch tells Scribe to run each job in Silent mode, preventing any screen messages from blocking processing:

c:
cd\
cd Program Files (x86)\Scribe
Tworkbench "C:\Customer Migration - 1-Accounts (Insert).dts" /RS
Tworkbench "C:\Customer Migration - 2-Contacts (Insert).dts" /RS
Tworkbench "C:\Customer Migration - 3-Accounts (Update).dts" /RS

Another approach is to chain the jobs together.  I open Job#1 and click on the Settings button on the toolbar.  Here I can tell Scribe to run Job#2 upon completion of Job#1.  I then open Job#2 and chain that one to Job#3.  You get the idea.  There are some additional parameters you can set as well:

image

When you kick off a chained job you get presented with the option to have a proceed/cancel prompt appear between each job or to just have them run in sequence without intervention:

image

There you have it, a fairly comprehensive introduction to Scribe Insight as a Data Migration tool for Microsoft CRM.  

This blog post has gotten away from me a bit but before I finish up I just want to touch on the small variation required to create an on-going Integration rather than a one off migration…

 

Scribe Insight for Integration Scenarios

Lets create a 4th DTS job to handle the on-going synchronisation of Contact data between the staging db and CRM.  I setup the source and destination connections and then chose Update/Insert as the Operation (I want the interface to handle both new records and updates to existing records):

image

I then click on the Operation tab and tell Scribe to perform an Update rather an Insert when it is able to match source field 11 (ContactID) to the destination field “new_externalcontactref”):

image

Then, it’s simply a case of mapping each of the source and destination fields just like I did before for the data migration of Contacts.  And I’m done:

image

 


If you have any experiences to share or have found this useful please leave a comment below.

Smile

Advertisements

5 thoughts on “Scribe Insight and Microsoft CRM 2011

  1. Hugo

    First of all, I should start by say thanks for your nice blog which I have been using for… I can’t even remember 🙂
    Secondly do you have a suggestion for what’s the best pattern/method to do the reverse?
    Rules are:
    – I want to send Contact data from CRM to a Staging table.
    – Asynch process: Users on CRM cant stop what they are doing. i.e. updating a contact

    The DTS to move data is easy to create, but my question is how to implement the “job” that monitors “something” (queue, crm field, datetime?) that will then execute the DTS to move the data.

    Thanks in advance for further help,
    Hugo

    Reply
    1. Gareth Tucker Post author

      Hi, I haven’t gone down this path with Scribe Insight but you can use the Scribe Console to set up Integration Processes that automatically run jobs when pre-defined events occur. In Scribe Insight go to the Help and on the Contents page have a look at “Creating a Collaboration”. I’d love to hear what you learn.

      Reply
  2. Deepika

    Hi gareth,
    From my experience Scribe insight is good but it is not so much user friendly . So we have changed to Inaport tool right now. I think inaport is much much better than scribe .They are even providing a migration pack which contains a set of profile. Once u installed means u can start work on the profile .Don’t even need developer skill. I like the tool very much.
    http://www.inaplex.com/Resources/videos/inaport-short-tour.aspx

    Reply
  3. JeffeVerde

    Where you need to populate a lookup to another entity (like the Parent Account thing), there’s an alternative to the Cross Reference Keys, that’s a little easier to set up.

    Do the import of Account, as you did. Then, in the Contact DTS, instead of the Cross Reference Keys thing, configure two Steps– a Seek to Account (using your keyfield), and an Insert (or Update/Insert) to Contact.

    After defining the steps, create a User Variable, pick the option for “step”, pick your Account Seek step, pick AccountID as the field, and give it a name like “ParentAccount”.

    In the mappings of your Update/Insert step, select the ParentCustomerID field, map it as a Formula, and in the formula editor, insert your “ParentAccount” variable.

    The end result is the same- you’re telling Scribe- “take this field from the source – find it in a different table in the target – grab a field from that target – use the found value to populate the record we’re creating/updating”.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s