Batch Processing

[Push2025]

Objective:

Read a CSV file which contains 2 columns:

• JIRASQL [to save the file as]
• FINALQUERY – SQL Syntax

Our goal is to run 46 SQL Queries and save each file with the name listed in JIRASQL.

Main Workflow:

Data Input Tool:  Connection to CSV File

2 lines feedling to Macro Tool, line 1 t Question Mark and Line 2 to other node.  Outnode has nothing.

Macro Tool: 

GroupBy Tab:  

Control GroupBy Field:  JIRASQL,

Input2 GroupBy Field:  JIRASQL

Questions Tab:

                                                Choose Field: JIRASQL:  JIRASQL

                                                Choose Field: FINALQUERY:  FINALQUERY

                                                Choose Field Inject FINALQUERY:  FINALQUERY

Batch Macro:

                Path:  Fully path of batch macro file.

                Batch Macro Button is selected

Engine: IterationNumber: 0 is selected.

Macro Input Tool

                File Input:  Full path of the file with JIRASAL and FINALQUERY

                Show Field Map box is checked

                Optional income connection is checked.

                2 lines coming out of Macro Input Tool.

Line 1 connected to SELECT which retains FINALQUERY and feeds into DYNAMIC Input Tool.

Line 2 is connected to SELECT Tool which retains the JIRASQL then to SAMPLE to ensure 1 record, then to RECORD ID to add a column ‘rowid’ to JOIN with output from Dynamic Input Tool.

Configuration of DYNAMIC Input Tool

Control Parameter is connected to Action Tool

ACTION Tool:

                                Select an action type:  Update Value

                                Value or Attribute to update:

                                                Dynamic Input – Modification – Modify – ReplaceText-value = [Query in SQL Editor]

                                Replace a specific string

                                                [query listed in SQL Editor]  

                DYNAMIC Input Tool:

                                Edit: Connected to SQL Server with a valid query in SQL Editor

                                Modify a Specific String:  Replace a Specific String:  [Query in SQL Editor]

                                Output is connected to JOIN Tool.

                JOIN Tool:

                                Connections from DYNAMIC Input & JIRASQL

                                Output is connected to Data Output Tool.

                Data Output Tool:

                                Write to File or Database:

                                                Full path as ‘Dummy_File.csv’

                                Take File/ Table Name:

                                                File Containing File Name or Part of the File Name: JIRA SQL

Summary:  When we have a 1 record to read, it gives valid results.  However, when we expand to more record, loops runs that many times but same query is run again and again, and results are appending to same file.

[apathetichell]

Hey -- -as I've mentioned a few times on these threads --- I hate Dynaminc Input. I use Dynamic Input In-DB That takes two fields 1) a query 2) a connection name. It runs In-DB. If you use that in your macro --- instead of dynamic input --- and you follow my directions---- you will not have any issues with schema differences between queries.

[Push2025]

Thank you so much for your patience and guidance.  I will do that now.  Sorry, I didn't do that yet.

[Push2025]

well, I am little embarrassed, but I don't' know how to develop a connection.  I have existing connection with SQL server, but I don't see it when I right click on connection line, neither I see ms sql server listed,

[apathetichell · Accepted solution]

let's start at the beginning --- do you have the driver downloaded and your odbc 64 connection set up? run test (assuming there is one) to check.

next --- go to alteryx --- got to advanced. go to manage in-db connections. set up a new in-db connection. You'll give it a name --- this is critical for the dynamic input-in-db -- since this is the second column you'll pass in.

you'll fill out info in the manage in-db and link this part to our odbc-64 profile you created. 

you can test this worked by dragging a connect in-db onto the canvas. hit connect. It should take a few minutes --- and cache your database objects. lots of database objects===more caching===more time on initial caching.

[Push2025]

Thank you so much.

I deeply appreciate all of your generous help.

Push