Loading Dynamics using CozyRoc SSIS tools

[Dave Stein]

I need to load approximately 225K contact records into Dyanmics. I need to check if the contact exists via exact match of First, Middle, and Last Name as well as address. If contact exists, update the record with additional information. If it is a new contact insert it. However, the destination system has well over a million contacts. 

The first attempt was to use a cached lookup against those fields from FilteredContact but that was overwhelmingly painful because of all the data which needs to be cached and the package failed a few times due to memory shortage. My second attempt was to join them in the OLE DB Source, but that query is painful as well. 

What is my best path here? Could I utilize the DetectDuplicate functionality of the CozyRoc destination and then route those failed records into a another CRM destination set to update?  

[Dave Stein]

Oh, and I did break the query down using the ContactBase and CustomerAddressBase tables which helps, but I'd still like to know if there's a better method. 

TIA. 

[Ray Riopel]

What is the contact data source?  I would load them into a SQL table so you can use cross-database joins to determine what needs to be inserted vs. updated.

[Ivan Peev]

Hi Dave,

Which version of COZYROC SSIS+ do you use? We have just released 1.6 version, which includes Upsert action. The action is based on the duplicate detection rule in CRM. You can setup the detection rule to use the fields you have listed below. If the CRM system reports back the current processed record is duplicate, it will automatically switch to insert action.

Ivan

p.s.

I think the approach with the DetectDuplicate option might also work. Its just that it requires a little extra effort.

[Dave Stein]

Thanks Ian, I'll give that a try.