SQL Commands in SQL Server Import flow

Comments

6 comments

  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Hi Richard,

     

    To do this, you'll need to do a few things. On your source step, you'll need a pre-save page hook in order to add in the page index and the record index. Then on your next step you would initialize your stage table and have an input filter where record index = 0 and page index = 0. This will ensure that the initialization of the table only runs once. Next you would create a step to insert or upsert your data into the stage table.

     

    After that, you will need another flow to run the merge function since I assume you only want to run it once after everything has been inserted. You will set your query on the second flow to limit it to 1 record, then create a step to run the merge command. I also created a few sample flows that I'll email over so you can install it into your environment and see what I mean.

     

     

     

    function preSavePage (options) {
      
      for (let [index, d] of options.data.entries()) {
        d.pageIndex = options.pageIndex;
        d.recordIndex = index;
      }
      
      return {
        data: options.data,
        errors: options.errors,
        abort: false,
        newErrorsAndRetryData: []
      }
    }

     

     

     

     

     

     

     

     

    Lastly, there is an idea in the product portal to natively have pre and post steps to handle these use cases. If you could give that a thumbs up and add any comments it will help prioritize it for the future!

    Happy holidays!

     

    0
  • Richard Brown

    Wow Tyler I'm impressed with your quick *and* complete response. I will explore your suggestions and let you know how it goes. It does seem a lot of work to submit a couple of SQL commands...the improvement suggestion sounds needed. Thanks a bunch!

    0
  • Richard Brown

    Hi again -- I used your techniques to get 2 flows working with my data -- thanks again for such complete descriptions. My only change was using simply "SELECT 1" for the second flow's export -- there is no need to even retrieve data. Next comes a real transfer and performance testing. Really appreciate your help.

    0
  • Richard Brown

    Here's a quick-and-dirty solution to adding pre- and post-Insert/Update SQL commands, at least for Microsoft SQL Server. Place the commands at the top and bottom of the Insert and Update commands, separated by a semi-colon. They will be executed before/after the Insert/Update.

    TRUNCATE TABLE SF4_Opp_Test;
    INSERT INTO SF4_Opp (
       Account_SA_Region__c,

    ...

       {{record.Atlas_Escalation_Memo__c}},
       {{record.Finance_Approved_Date__c}}
    );
    EXEC NS_Integration.dbo.SF4_Opp_Test_Post_Proc;

    Note the commands are executed for *every* record -- if you need/want the commands to execute only once for the batch of records, use Tyler's technique above.

    1
  • Scott Henderson CTO
    Celigo University Level 1: Skilled
    Answer Pro
    Top Contributor

    I would recommend doing this pattern via 3 separate flows.

    • Flow 1: Initialize SQL steps
    • Flow 2: Processing data SQL steps
    • Flow 3: Finalize SQL steps

    For "flow 1" and "flow 3", you can use a generic "select 1" query for the source bubble, and then you can do any number of setup or closing SQL commands in those flows. Obviously, link all the flows to run one after the other.

    Using separate flows is easy to setup, and saves you from writing any JavaScript, and also keeps the different types of SQL separated in their own flows.

    0
  • Richard Brown

    Hello Scott -- thanks for your alternative suggestion. It is more elegant and highlights how the SQL steps can be repurposed as command steps, opening up all sorts of SQL manipulation. Unfortunately we have a limit on flows in our Celigo package, so increasing from 2-flows-per-integration to 3-flows-per-integration gives me pause. We will review how this affects our future plans.

    0

Please sign in to leave a comment.