SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Determine which columns should be updated during an incremental load


Determine which columns should be updated during an incremental load

Author
Message
Paul Hernández
Paul Hernández
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 661
Hi Folks,

I have the following Scenario: I receive customer data every day from an external Company over an ETL process. I store this data in a database called the Data Hub. The customer table has two columns to identify new and updated records: CreateDt (timestamp) and UpdateDt (datetime). I need to load these data into a Dynamics CRM. For the Initial load all is clear, but to perform incremental loads over the CRM entities I've proposed the following strategy: if a record has a CreateDT or UpdateDT subsequent to the last loading should cause an insert or an update respectively. The whole row content should be in the correspondent CRM entity updated.
Up to here everything seems to be ok. The problem is, the CRM guys have complained Cool, because the load is performed over a Web Service using a CozyRoc component and the process is very slow. Furthermore, the update of some fields Trigger several CRM internal procedures. So they ask me to update only the fields that have changed.
I think this is time expensive too, and I couldn't figure out how I could do that without comparing row by row and column value by column value. I have about 20 columns. I can dynamically create each query checking the value column by column but for me this is the Flintstone’s Style way, and I find it even worst as the CRM latency Problems;-).

Any other proposed solution? It will be appreciated it.

Kind Regards

Paul Hernández
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19426 Visits: 20462
Can you get to the database directly, or do you have to use the web service method?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Paul Hernández
Paul Hernández
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 661
Hi Phil thanks for your answer.

Now I'm trying to persuade my colleagues to go directly to the database and not over the web service. They allege that write directly is unsupported and I say that the web service for this task is like to go deep in the jungle. The major problem is any modification in any CRM attribute triggers an activity (modification, modified by, modified on). Some fields have additional events.
Even worst, I cannot dynamically configure the mappings in the component that calls the web service.
I will try to convince them to use dynamic SQL queries intead.

Kind Regards,

Paul Hernández
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19426 Visits: 20462
Paul Hernández (4/4/2013)
Hi Phil thanks for your answer.

Now I'm trying to persuade my colleagues to go directly to the database and not over the web service. They allege that write directly is unsupported and I say that the web service for this task is like to go deep in the jungle. The major problem is any modification in any CRM attribute triggers an activity (modification, modified by, modified on). Some fields have additional events.
Even worst, I cannot dynamically configure the mappings in the component that calls the web service.
I will try to convince them to use dynamic SQL queries intead.

Kind Regards,


I suspect that they are correct in their allegations of no support for direct access. But that sure makes things difficult for you - those 'triggers' are presumably set off by the web service call and that's a black box - you don't know what they're doing, other than by observation.

I'll sign off at this point because I don't think I can help. I would not expect the web service method to ever be quick for bulk update operations, so matter how much you refine things - maybe you should cross to the dark side and frequent a CRM forum to see whether they have any suggestions.

Actually, I did have one idea. Writes to the database may be unsupported, but what about reads? These reads could, perhaps, be used to (relatively) quickly identify which columns need to be updated, with just the updates being pumped through the treacle-like update web service.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search