Batch macro to change a query dynamically

[rodalferreira]

Hi guys,

I have a workflow which I am using a dynamic input tool connected to SQL Server and with a query that looks like:

SELECT *
FROM TABLE_YYYYMM
WHERE CUSTOMER = 'xyz'

In this dynamic input I am replacing YYYYMM with a correct period like 202101 and also replacing 'xyz' with the correct customer code.

According to the user interaction (there is an app wrapping up all together), I have to run multiple periods with multiple customers,  something like:

PERIOD CUSTOMER
202101   321
202101   867
202102   321
202102   867
202103   321
202103   867
202104   321
202104   867

In the dynamic input I have the results I need, but I can't publish in my server because the dynamic input holds a connection in the tool. I'm starting with batch macros but I am struggling and I am not sure if that's the best solution.

Any ideas?

Thanks

[jonnyrask]

Hi @rodalferreira - I'm not exactly sure what the issue is here, but I do have a question for you.

Why are you using a dynamic input tool?

Here's what I would do - create the batch macro that has the input that connects to sql server. In the macro, have a control parameter and action to change the sql statement with the Period and Customer.

Maybe attach your wf or send a screenshot.

[rodalferreira · Accepted solution]

Thanks @jonnyrask  for your reply.

A found out an alternative to keep using the dynamic input and have the credentials fixed. I'm using the dynamic input because I can have the query in there and replace parts of that with the period I need, previously calculated.

Appreciate your thoughts.