Repeating a separate process during the execution of an iterative macro

[Archaeopteryx]

I'm running API calls and saving data to a SQL Server. There is an iterative macro involved which runs an API that includes an ID number. Each iteration of the macro produces a new ID number until the data request has exhausted all id numbers.

I need to run a SQL Server stored procedure between each iteration of the macro calling that Stored Procedure. 

I have the SP set up in an Input Tool. And it is in its own workflow.

Can someone assist as to how that SP can be called in between each iteration of the macro? I have tried the Runner CReW macro, but it seems to halt the iteration of the macro and it hangs. 

Thank you,

Chris

[MattBSlalom]

I'm unclear on the intent of the procedure in question.  If it's needed per iteration, why can't it just be added into that macro vs it's own workflow?

[danilang · Accepted solution]

Hi @Archaeopteryx 

As @MattBSlalom suggested, add the call to the SP in the iterative macro.  Use a Dynamic Input as opposed to an Input tool so that you'll be able to place the tool wherever you need to in the workflow.  The Dynamic Input needs to have at least one option set, so check the Modify SQL Query option and Pass a Field to The Output.   

Just make sure you use a Summarize or Sample tool before the Dynamic Input so that you only call the SP once.

Dan

[Archaeopteryx]

Matt,

I ask because I don't know. 

[Archaeopteryx]

Danilang,

Which field would I pass? I have three fields as parsed JSON. "Fields", "Data", "Record ID". 

[Archaeopteryx]

Hi Danilang,

I kept experimenting and found I must pass all fields I want processed through the Dynamic Input tool, or the fields won't come out the output lead of the Dynamic Input tool. I experimented first on an abbreviated version of the workflow and the SP seem to be working, I'll have to consult with a partner and have him check the SQL Server end of it. 

I then applied the Dynamic input to the macro. The Results pane hangs there with a message that 12 records per minute are being processed. 

" 1ST-GENESYS-API Req for Conv&CursData-CURSOR-MACRO (960) Iteration #1: Tool #4: Records transferred: 1; active connections: 1; average records per minute: 12"

Since there are many thousands of records, if I believe what I'm reading, this workflow will finish sometime in May. It could be hung but I have no way of knowing until the SQL resource looks on their end. 

Your thoughts?

[Archaeopteryx]

Danilang,

After much experimenting well into last evening, the solution for this was not to run the SP between each fetch of a unique identifier pull of the API request, but rather, to capture each iteration of the Identifier fetch, then, use that number to append to the parsed json record ID number to create a unique record id number across all identifier iterations for the entire run of the workflow, then, UNION all output into one bulk upload to SQL. Then, run the SP once at the end of the run using the standard Input Tool. 

Designer 2020.3 was getting stumped on the Dynamic Input tool and the passing in of all the fields I needed to be processed once we entered the macro. 

But, your suggestion got the ball rolling, so thank you.

Chris