locked
CRM SSIS integration insert/update failure RRS feed

  • Question

  • I have two different CRM organizations. I am trying to export contacts from one organization to the other and on regular basis(a job). It has to update the existing records and insert the new ones every time.

    So, I've used SSIS to create a package and successfully integrated CRM1 database to CRM2(web UI through script component) and inserted the contacts. I've not created the job to schedule yet.

    My problem is, whenever I am running this package manually for testing purposes, it is inserting all the contacts all the time(inserting duplicates each time). I guess it is because I didn't do any tranformation in the data flow.

    I am fairly new to SSIS, so please bear with me. Now, I am trying to avoid these duplicates using lookup transformation in the data flow task but it is failing and inserting the duplicates whatever I do.

    This is my script component:
    public class ScriptMain : UserComponent
    {
        private OrganizationService _orgService;
    
        public override void PreExecute()
        {
            try
            {
                base.PreExecute();
    
                var connection = CrmConnection.Parse(@"Url=https://abc.com; Username=xxxxx; Password=*******;");
                _orgService = new OrganizationService(connection);
            }
            catch (FaultException ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    
       public override void PostExecute()
        {
            base.PostExecute();
            /*
             * Add your code here
             */
        }
    
        public override void ContactInput_ProcessInputRow(ContactInputBuffer Row)
        {
            var contact = new Entity("new_contact");
            contact["new_name"] = Row.fullname;
            contact["new_language"] = Row.newprimarylanguage;
            contact["new_email"] = Row.emailaddress1;
            contact["new_primaryphone"] = Row.mobilephone;
    
            _orgService.Create(contact);
        }
    
    }
    And in the Lookup Transformation:
    I gave Cache mode as Full Cache and Connection type is OLE DB Connection manager.
    "Redirect rows to no match output" for handling the rows with no match.
    In Connection, I gave the CRM2 database table information for lookup with CRM1(data source).
    In Columns, I mapped  the above fields. These are all the configurations I done for Lookup Transformation.

    Can someone please look at it and let me know what I am missing. Thanks
    Monday, February 9, 2015 8:48 PM

All replies

  • The Lookup Transformation does not handle nulls particularly well. If the fields have nulls in them, it is best to replace them with empty strings:

    In the Connection to CRM 2in the Lookup, set the source as a SQL statement, and use the Isnull function to convert the nulls for each field, e.g.

    Select isnull(new_name, '') as new_name
    , isnull(new_email, '') as new_email
    
    from contact

    In the data you get from CRM1, use a Derived Column transformation to replace each column, using the REPLACENULL function.

    You can also add DataViewers within the SSIS DataFlow to check what data is in the buffer in each stage


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Tuesday, February 10, 2015 10:18 AM
    Moderator
  • Hi, one possibility is to filter out the duplicates before running the list through your script component.

    ie I assume you use a normal SQL query to fetch the one side of the data, you could check on the other table at the time of the call.

    To make it even quicker you can maintain a list of Guids that are on both sides in an accessible area and use that list to check before fetching the data.

    Then when you insert into the other side you can update the list.

    Just some idea's

    regards,

    Donald

    Tuesday, February 10, 2015 1:17 PM
  • Thanks guys, those are some good points to consider.

    I was able to pull data without duplicates in CRM2 by using a lookup transformation in the dataflow package. So that No Match data goes to the script component and Match data goes to an excel sheet. But still, if I update an existing contact it creates a duplicate contact with the change made in it. How do I call update method to check and see if the data is in there and a change has been made?

    Thanks

    Tuesday, February 10, 2015 4:23 PM
  • I figured out to use _orgService.Update() method to check if data exists in the records and then update. But then I realized the CRM1 entity and CRM2 entity has different unique identifiers which I cannot use to compare and update the records. what should I do in this case?

    Thanks


    Tuesday, February 10, 2015 9:36 PM
  • If the records in CRM2 are only created by this process, then you can ensure they have the same unique identifiers by setting the Id property of the contact instance you create to the contacted of the existing contact.

    Otherwise, you'll need some other field of combination of fields to match on. You could create your own, with maybe an autonumbering plugin to generate a unique value, or you could decide that a combination of other fields (maybe email address, or phone number) is close enough to match on


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Wednesday, February 11, 2015 6:21 PM
    Moderator
  • I would use the CreateRequest and CreateResponse classes to create your contact records. There is a parameter setting that you can use to enable duplicate detection when performing the create. That way, the create would fail and you can check the CreateResponse message that returns to verify it is failing due to duplicate detection.

    Here is an MSDN article that explains how it works (and with a sample).

    Good luck!

    John

    Thursday, February 12, 2015 5:34 AM