How to migrate on premises multiple SQL database tables to snowflake by creating pipeline.

[Puneet13]

I’m working on designing a Batch Macro in Alteryx to automate the migration of multiple tables from an on-premises SQL database to Snowflake. My goal is to store all table names in a single Text Input, dynamically execute a query for each table, and load the results into corresponding tables in Snowflake. Despite several attempts, I haven’t been able to get the macro to function as intended. Could someone guide me through the correct design or share best practices for implementing this workflow? and i m not able to share the sample workbook as company restrictions.

[caltang]

Your concept is right, but you are building a batch macro incorrectly. You are executing an entire table's worth of paths into one dynamic input which takes in a dummy or first path, and then writes to Snowflake for that single table's worth of values.

To fix this, you need to implement the batch macro AND a different workflow to use that batch macro to then get you your data into the format you need BEFORE you load it into Snowflake.

This guide illustrates: https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309 

Now, once you solve that batching process, you need to think about your Snowflake output. You can write the output and create the table / upsert etc AFTER the output of the batch macro in your other workflow as a one shot thing. So you don't really need to have the control parameter or action the final output tool per se.

[Puneet13]

HI Just wanted to know how this run with multiple SQL table put in Text input and call it using dynamic input and them output to the snowflake, if you can help share with me some sample. 

[caltang]

I just told you what you were doing incorrectly and how you can do it, plus a concept guide was linked to you @Puneet13 

[apathetichell]

Follow this thread --> https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Batch-Processing/m-p/1398528#M...

[Puneet13]

can somebody share the sample workflow where multiple sql table from database put in snowflake.

[Puneet13]

can you please help and share the sample workflow it would be a great help

[apathetichell]

Hey @Puneet13 --- sorry -- no license - but try it yourself -- and let us know where you are hitting an issue. Also--- reminder - mark the solution provided by the other poster as the solution - not your own. I noticed that both times you've accepted solutions - you've accepted your own solution not the solution provided by the other poster. Hopefully unintentional because that's a jerk move.

[Puneet13 · Accepted solution]

Its done, i have achieved it using batch macro with directory and formula tool

[apathetichell]

Updating prior comment:

I've noticed that everytime you've accepted a solution - you've accepted your own solution not the solution provided by another poster. That's a jerk move.