…I got a call from one of our project team members regarding the Scribe packages that had been built for a client.  Apparently, the client needed us to migrate over data from system protected fields to maintain valid historic values. Ok, I’m down with that.

Having been involved in the upgrade process and hurdles with this client at earlier stages, I was aware that they had issues with AD users.  Having come from a history of CRM 1.0, I could feel the scars of the past tingling.  My mind was racing… “deleted AD users = orphaned SystemUser GUIDs…orphaned GUIDs = ‘Object not set to an instance of an object’ errors…”  Ughhh.

I quickly determined a default user to use in the case that the source user had been deleted, then proceeded to set variables using the cross reference key values stored in the Scribe Internal database from when the SystemUser entity rows were written originally to the target.  As basic as it seems, the formula called XREFLOOKUPNOLOCK_BKEY([label]”SystemUser”, S[x]).  Not so magically, the return value of the formula pulled the new CRM 2011 SystemUser GUID where it found the matching CRM 4 SystemUser GUID in the cross reference table.  Bam!

Now that I had the GUID values that would either match to an active user lookup value or fail to the default system user, I had to find a way to write the data into the target.  Remember, these are system protected fields, so we all knew the Scribe CRM Adapter would be a bust….

Understanding that the Scribe Adapters for CRM communicate through the presentation layer and not directly to the data layer, I knew that the CRM SDK (the foundation for the Scribe Adapter) would not allow an Update function against these system protected fields (hence they are system protected!).  Thinking it would be easy to change my adapter to use the OLEDB connection to the CRM database (yes, I know it’s unsupported), I quickly pointed my source to the new target connection and Viola! all test rows passed successfully.  Job done; go home, right?  Not really.

While the rows all seemed to pass through the OLEDB connection fine, they didn’t get committed to the database completely.  So, since we had already gone down the path of unsupported methods, we decided to go with a straight TSQL script.  However, since we had already validated our lookups and defined what our static source data elements would be, we got a little creative.  Rather than write a set of TSQL nested cursors to do lookups and validations, we added a secondary target to all of our Scribe packages which contained all the key value pairs between the CRM 4 and CRM 2011 records.  We sent this master bridge information to a custom table we created in the Scribe Internal database and used that as our source for a single tiered cursor.  Not only did we mitigate the execution time of a would-be hectic cursor in SQL, but we also created a veritable audit log for the migration effort by safely storing the migration mapping values!

Now that the stage was set, I passed my bridge table information along to our script master.