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?
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.
On Thursday, July 12, 2012 9:25:43 AM UTC-5, Dave Stein wrote:
> 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?
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.
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.
On Thursday, July 12, 2012 10:25:43 AM UTC-4, Dave Stein wrote:
> 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?
On Thursday, July 12, 2012 7:47:07 PM UTC-5, Ivan Peev wrote:
> 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.
> On Thursday, July 12, 2012 10:25:43 AM UTC-4, Dave Stein wrote:
>> 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?
