Push data from on-prem sql server view into cloud based MS Dynamics CRM database

  • madEG

    Valued Member

    Points: 56

    Hello,

    First, I apologize, I don't speak cloud.  But I'll do my best 🙂

    In short, I have a sql server 2012 view that I need to get to a cloud based MS Dynamics CRM system, so this system can this data as reference data.

    In early talks with the developers of this system (consultants) I thought that we agreed that I would export the view's data to a csv, and put (ftp) the file somewhere, and then the CRM folks would wave a magic wand over it, and it would get inserted/update the appropriate data in this new system.  (I thought they would give me credentials and an IP address, I would schedule a put of the datafile and walk away...)

    Now it seems, per them, that I can't merely ftp the file to somewhere where it can be acted upon, but instead I need to run some sort of a process on my sql server system (or something here on-prem) that will push and insert the data into the cloud based CRM system.  

    The folks I'm working with say that CRM can't be taught to "look to an area on a schedule and see that a new data file is available, and update/insert the contained records" and I must have a client (of some sorts) present the inserts to the CRM system.

    It's almost like they are saying, I can't place the package of M&M candy in the CRM system's pocket for it to eat when it gets hungry, but I must place each piece of candy individually in the system's mouth one at a time - since CRM has no hands.

    Ok - that analogy is a bit silly, but that is is my best takeaway.

    Can someone help me make heads or tails of this?  I'll be happy to read anything I can, but I'm not sure if what I am being told is true, or only true partly...  or what.  To make it worse, I have effectively zero experience with anything not in my own server room - so cloud based challenges have not been on my radar.  I'm pretty ignorant to this challenge.

    Thanks so much for a push in the right direction!

    -Matt G.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    madEG - Friday, July 20, 2018 12:56 PM

    Hello,

    First, I apologize, I don't speak cloud.  But I'll do my best 🙂

    In short, I have a sql server 2012 view that I need to get to a cloud based MS Dynamics CRM system, so this system can this data as reference data.

    In early talks with the developers of this system (consultants) I thought that we agreed that I would export the view's data to a csv, and put (ftp) the file somewhere, and then the CRM folks would wave a magic wand over it, and it would get inserted/update the appropriate data in this new system.  (I thought they would give me credentials and an IP address, I would schedule a put of the datafile and walk away...)

    Now it seems, per them, that I can't merely ftp the file to somewhere where it can be acted upon, but instead I need to run some sort of a process on my sql server system (or something here on-prem) that will push and insert the data into the cloud based CRM system.  

    The folks I'm working with say that CRM can't be taught to "look to an area on a schedule and see that a new data file is available, and update/insert the contained records" and I must have a client (of some sorts) present the inserts to the CRM system.

    It's almost like they are saying, I can't place the package of M&M candy in the CRM system's pocket for it to eat when it gets hungry, but I must place each piece of candy individually in the system's mouth one at a time - since CRM has no hands.

    Ok - that analogy is a bit silly, but that is is my best takeaway.

    Can someone help me make heads or tails of this?  I'll be happy to read anything I can, but I'm not sure if what I am being told is true, or only true partly...  or what.  To make it worse, I have effectively zero experience with anything not in my own server room - so cloud based challenges have not been on my radar.  I'm pretty ignorant to this challenge.

    Thanks so much for a push in the right direction!

    -Matt G.

    Quick questions, how large is the data set and how large are the daily deltas?
    😎

  • frederico_fonseca

    SSChampion

    Points: 14053

    Issue with MS CRM is that you cannot insert data directly onto their tables as doing so will void the maintenance contract.
    if this is a PasS or SaaS you don't even have access to the database itself.
    So the way to insert data is to use the supplied CRM SDK. Which can be quite slow depending on the volumes of data.

    Plenty of examples on CRM site - here is one https://msdn.microsoft.com/en-us/library/gg328416.aspx

    You may also, depending on what the requirements are, be able to use one of the available CRM SSIS  connectors - although these may end up being even slower, they possibly will require less knowledge of CRM and of how to use the SDK.

  • madEG

    Valued Member

    Points: 56

    Hello, and thanks in advance!

    The data load is about 460 records.  The data file (with commas and quotes) is about 90KB - so, not large.

    If I could wave a magic wand the updates would replace the entire data set (the ~460 records) nightly, so I wouldn't have to manage "what changed."   If I were to track actual changes, I would guess maybe two new records per week, and maybe five to ten field value changes.  It's fairly static data.

    Per user specs, the new system doesn't need to know if a formerly used record is no longer in the reference data table that I am trying to populate.  I guess they use the data, and then don't reference it again.   The data is contact info that is used for one-off letters to folks, with the letters being stored, so the need to reference what was used is not there.

    I'm looking for "easy to implement and maintain" over speed, since the updates/inserts would only need to happen once per day and likely off hours.

    Thanks!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply