SQL Commands in SQL Server Import flow
Hello All -- I would like to execute some SQL Commands -- like initializing a table and running a post-import merge -- and do this before and after the Insert and Update commands in my SQL Server Import. Is there any way to create stand-alone SQL actions separate from the Import?
Thanks for any info.
-
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 -
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 -
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 -
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 -
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 -
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.
Comments
6 comments