MS CRM transformation - 05-24-2005 , 04:02 PM
I am beyond my capability in trying to move MS CRM accountbase data via
the CRM Data Migration utility.
My difficulty is in handling the Uniqueidentifier Primary Key
"AccountId" guid from the originating table
and passing that 16 character GUID to an intermediate table which has a
Primary Key "AccountId"
which maps to "AccountId" Int type 4 bytes.
I have attempted the use of DTS to copy data from orginating table to
intermediate table and every attempt
throws an error at the AccountId row. I have read some of the threads
on this site and it appears there
must be a work around this. I have tried to change the Data Type of the
GUID to Int using VB script during tranformation and this fails. I have
also attempted to change the design of the intermediate table so that
AccountId would be a matching Uniqueidentifier but cannot.
Any assistance would be appreciated. I know there are 3rd party
conversion programs, such as customereffectives'
but I would rather have the knowledge. Thanks.
Re: MS CRM transformation - 05-24-2005 , 04:33 PM
Its been 2 years, and I only did it once but I had to migrate from
salesforce.com to MS CRM 1.0 for a client. SF.com exported to an Excel
spreadsheet, which was the basis for my conversions. I imported those
spreadsheets to a staging SQL Server database, then used this:
to find each GUID wherever it existed in all the tables and replace it with
All of this was done in a loop that went through each table and grabbed the
next GUID, as well as incremented a counter for the INT.
It took forever, but it worked. No idea if it will help you.
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
<bchodorov (AT) hotmail (DOT) com> wrote
Re: MS CRM transformation - 05-26-2005 , 04:23 PM
Just a follow up to this plea for assistance! First, my thanks to Kevin
Hill for the reference and the realization that someone else had
successfully accomplished the task I was seeking help on.
This was my first go around on DTS. Awesome little utility. I could
have purchased Scribes package that takes db data and transfers same to
MS CRM. But a 45 day license costs upwards $500. Not gonna do it!
Basically, my difficulties lay in my lack of understanding of Scripting
and DataType conversions. Not to mention NOT NULL fields! Days later I
have a better handle on what XCopy in DTS can (simply) handle. Why I
have to be careful with those NOT NULL fields - either omit mapping in
dts or create script to create new UNIQUEIDENTIFIER, which I think I
copied from here. Then fiquring out the insanity of MS' use of GUIDs
all over the Accountbase table for controlling identity. Plus how to
put my own GUID in with Update Queries to match the new database I am
importing into. Hope this sounds intelligable to you gurus out there.
Okay, so now what do I do with my new found knowledge and MORE
IMPORTANTLY does anybody think it of value? If so, I can use the work
to transfer an Act, Goldmine, Access or flat file into MS CRM so I can
better cut my teeth on this...