locked
Need a Lil Help making sure my syntax is right. RRS feed

  • Question

  • Background: 

    Microsoft CRM 4.0 Rollup 11
    MS Server 2008R2
    Quad Core 8GB 1TB Storage
    MS SQL Server 2008 SP1

    Been trying for a few hours now trying to figure out how to import an account AND contact info, have them resolve to each other via Primary Contact and Parent customer.

    The original Problem I've been having is that every now and then I have to import approximately 30K Accounts and Contacts for said accounts.  
    Importing the Accounts First Worked Flawlessly.  No Problems.
    When I tried to upload the Contacts Mapping the Parent Customer Field to the related account, FAILED FAILED FAILED.

    The Process where I map the Parent Customer Field to the related Account works with about 10 to 50 records.

    BUT when doing 30K+ records per import, it does NOT a fun afternoon maketh.

    So I said Fine and mapped the Account (Which would have been Parent Customer) to ExternalUserIdentifier.  30K contacts import no problem.

    So now I have to Update the Parent Customer on the Contact to resolve to Customer info in the ExternalUserIdentifier Field.
    Came up with this lil update query here and hope it's right.

    UPDATE  ContactBase
    SET       AccountId = AccountBase.AccountId
    FROM     AccountBase INNER JOIN
               ContactBase ON AccountBase.Name = ContactBase.ExternalUserIdentifier

    This should work??? I Hope???  

    Thanks Tons in advance....

    Monday, June 21, 2010 7:18 PM

Answers

  • I don't recommend direct updates to the SQL database as it is not supported.  You'll want to populate the Contact record's parentaccountid.

    For future consideration, consider using the Data Migration Manager if you are not already using it.  Check out this good tutorial for importing related records using the data migration manager.

    To update existing records, if you want to use SQL, then you will need to have a look at the SDK and develop your script based on SDK supported methods.  Otherwise you can use Scribe, Ebax and check out the Bulk Update tool on codeplex.

     


    Regards, Donna

            Windows Live Blog

    • Marked as answer by Jim Glass Jr Wednesday, June 30, 2010 8:53 PM
    Monday, June 21, 2010 9:54 PM
  • Hi Nick, Donna is right -- the Data Migration Manager tool is useful for importing records. It's from Microsoft and it's free, so definitely worth trying out, and if you are doing imports frequently it's worth learning how to use.

    However, you could achieve your requirement using the Bulk Import Wizard, especially as you are using UR11 (an improvement was made in UR9).

    1. Import your accounts

    2. Import your contacts, with the Parent Account referring to the imported account name

    3. Export your accounts (the accountid will be contained in a hidden column A). Delete all the other columns. Add a new Primary Contact column and use an Excel vlookup to add the appropriate contact name. If you have lots of contacts with more than one name, you'll need to use the contactid (which you can get by exported all the contacts that you imported in step 2).

    4. Weirdly, CRM Import Wizard only accepts GUIDs without curly braces {}, so you'll need to remove these from your CSV file.

    5. Now when you use the CRM Import Wizard to re-import your accounts, it'll use the accountid and update the existing account records with the primary contact.

    Hope this helps.

    Neil


    Neil Benson, CRM Addict at Customery Ltd.You can reach me on LinkedIn or Twitter, and you're welcome to sign up to Customery, an online community for UK-based Dynamics CRM people.

    • Marked as answer by Jim Glass Jr Wednesday, June 30, 2010 8:53 PM
    Tuesday, June 22, 2010 11:32 AM
    Moderator
  • "The DMM doesn't work with SQL 2008 though so how do I get around that limitation??"

    The DMM works with SQL 2008 if you select the local option when you install.  You don't want to select the option to connect directly to the CRM SQL database.  Web services will move the data between the local SQL instance and SQL 2008.


    Regards, Donna

            Windows Live Blog

    • Marked as answer by Jim Glass Jr Wednesday, June 30, 2010 8:53 PM
    Wednesday, June 30, 2010 4:46 PM

All replies

  • And a little other side note, because we have multiple contacts to one account being imported, I really can't/don't want to set a primary account at this time

    (Or at least I don't think so)

    Monday, June 21, 2010 7:20 PM
  • I don't recommend direct updates to the SQL database as it is not supported.  You'll want to populate the Contact record's parentaccountid.

    For future consideration, consider using the Data Migration Manager if you are not already using it.  Check out this good tutorial for importing related records using the data migration manager.

    To update existing records, if you want to use SQL, then you will need to have a look at the SDK and develop your script based on SDK supported methods.  Otherwise you can use Scribe, Ebax and check out the Bulk Update tool on codeplex.

     


    Regards, Donna

            Windows Live Blog

    • Marked as answer by Jim Glass Jr Wednesday, June 30, 2010 8:53 PM
    Monday, June 21, 2010 9:54 PM
  • Thank you Donna....

    The DMM doesn't work with SQL 2008 though so how do I get around that limitation??

    And I used the Bulk Update Tool to get some CSV files I needed for a documentation process, but I guess I just need to know how to update the CRM files with what I need as the directions on the update tool are few and far between.

    Tuesday, June 22, 2010 11:31 AM
  • Hi Nick, Donna is right -- the Data Migration Manager tool is useful for importing records. It's from Microsoft and it's free, so definitely worth trying out, and if you are doing imports frequently it's worth learning how to use.

    However, you could achieve your requirement using the Bulk Import Wizard, especially as you are using UR11 (an improvement was made in UR9).

    1. Import your accounts

    2. Import your contacts, with the Parent Account referring to the imported account name

    3. Export your accounts (the accountid will be contained in a hidden column A). Delete all the other columns. Add a new Primary Contact column and use an Excel vlookup to add the appropriate contact name. If you have lots of contacts with more than one name, you'll need to use the contactid (which you can get by exported all the contacts that you imported in step 2).

    4. Weirdly, CRM Import Wizard only accepts GUIDs without curly braces {}, so you'll need to remove these from your CSV file.

    5. Now when you use the CRM Import Wizard to re-import your accounts, it'll use the accountid and update the existing account records with the primary contact.

    Hope this helps.

    Neil


    Neil Benson, CRM Addict at Customery Ltd.You can reach me on LinkedIn or Twitter, and you're welcome to sign up to Customery, an online community for UK-based Dynamics CRM people.

    • Marked as answer by Jim Glass Jr Wednesday, June 30, 2010 8:53 PM
    Tuesday, June 22, 2010 11:32 AM
    Moderator
  • Thank you Neil...

    But Step 2 is what was causing me fits.  

    It wouldn't allow me to upload the contacts with the Parent Account attached to it.
    in very Small quantities, it would allow me to do that, but with the 30K that I had, it just gave a failure before even getting to the Transformation stage of the import.

    The CSV File is a small file too.... 2.4 MB

    Anything I should be checkign on the SQL server side to see why this is happening???

    Tuesday, June 22, 2010 6:51 PM
  • "The DMM doesn't work with SQL 2008 though so how do I get around that limitation??"

    The DMM works with SQL 2008 if you select the local option when you install.  You don't want to select the option to connect directly to the CRM SQL database.  Web services will move the data between the local SQL instance and SQL 2008.


    Regards, Donna

            Windows Live Blog

    • Marked as answer by Jim Glass Jr Wednesday, June 30, 2010 8:53 PM
    Wednesday, June 30, 2010 4:46 PM
  • Hi - I've imported my Accounts, followed by my Contacts with the Parent Customer which worked fine.  Now I want to set the Primary Contact on the Accounts so I did what you have written, i.e. I exported my accounts, I remove all fields except for Column A (the GUID) and Column B (Account Name -- it seems to need this column) and I actually removed all rows except one for a test.  I then exported my contacts, chose the GUID for the contact that should be Primary Contact on that account and added it to the column called Primary Contact in my Excel spreadsheet.  I then removed the curly braces from each field, saved the file and then converted it to CSV format.

    When I imported the CSV, CRM already knew that I was importing accounts, that the data mapping was automatic, that I was enriching text and that it should be assigned to me.  I chose to allow duplicates.  It looked like the import worked, i.e. it said successful but when I looked at the account, it still didn't have the Primary Contact.

    Any suggestions?  I'd really like to use the Import Wizard rather than the Data Migration Manager.

    Thanks very much.


    Positively
    Monday, November 29, 2010 8:08 PM
  • Try adding the Full Name of the Contact or the Last Name, First Name fields to the file in addition to the Contact Guid and let's see if that works. 

    Regards, Donna

    Monday, November 29, 2010 9:52 PM
  • I've just tested a couple of dozen different permutations of the enrichment procedure to try and update the Primary Contact or Parent Account of an account record, and none of them succeeded. I was able to specify the lookup values on initial import but not with subsequent enrichment. I tried GUIDs, full names, "objecttype,GUID", etc. I also tried installing UR14 and reverting back to baseline.

    Sorry Positively, looks CRM 4.0 is unable to meet your requirement.

    Neil


    Neil Benson, CRM Addict and MVP at Customery Ltd. You can reach me on LinkedIn or Twitter. Join over 10,000 other CRM professionals on the Microsoft Dynamics CRM group on LinkedIn.

    Monday, November 29, 2010 11:25 PM
    Moderator
  • Since Neil has tested various scenarios with the Import Wizard and it does not work the way we hoped, other options that come to mind follow:

    • Use the SDK to write supported SQL scripts to do the updates
    • Use Scribe
    • Use Ebax
    • Use Codeplex Update tool to make the update
    • You can delete or deactivate the Account records and re-import them with the Primary Contact.  If you use this method, I recommend using a workflow to set the Account records to Inactive and modify the Account Name to something like 'inactive + Account Name".  This will prevent future issues with Duplicate detection, user confusion, etc.  You can also simply delete the Account record if you don't have any other records linked.  This could mean that you will need to re-import your Contact records if they are referencing the imported Account records.

    If none of these options work for you and you can think of no others, then you will need to have someone make the updates manually.


    Regards, Donna

    Tuesday, November 30, 2010 12:09 AM
  • I've just tested a couple of dozen different permutations of the enrichment procedure to try and update the Primary Contact or Parent Account of an account record, and none of them succeeded. I was able to specify the lookup values on initial import but not with subsequent enrichment. I tried GUIDs, full names, "objecttype,GUID", etc. I also tried installing UR14 and reverting back to baseline.

    Sorry Positively, looks CRM 4.0 is unable to meet your requirement.

    Neil


    Neil Benson, CRM Addict and MVP at Customery Ltd. You can reach me on LinkedIn or Twitter . Join over 10,000 other CRM professionals on the Microsoft Dynamics CRM group on LinkedIn.


    Neil,

    This thread adds some insight to this process.  It was discovered by another user that the field is supported by an ancillary "Update" call after the original "Create" call; but you're saying that apparently this process is applied only to new records, and not for enriching existing ones?  I find that rather odd.  I would consider that a bug.  Since you tested it, would you drop an item into Connect and mail the MVP list (or me directly) for voting?


    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Tuesday, November 30, 2010 1:02 AM
    Moderator