How to update Dynamic input tool's SQL connection as well as table to use dynamically

[utkershgupta]

I am having a situation, where both the SQL database string as well as SQL tables are changing. Example Select table A from Database B, or it can be Select Table C from database D and these databases B or D can be on different servers X or Y. I have made a connection variable by using formula tool and added the query also in it like:

"odb:Provider=SQLOLEDB.1;Password=__EncPwd1__;Persist Security Info=True;User ID=sa;Initial Catalog=XXX;Data Source=YYY;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ZZZZ;Use Encryption for Data=False;Tag with column collation when possible=False;"

I am using it as a variable in Dynamic input tool and choosing option as "Read a list of data source" and in that Field is my connection variable from file and Action would be "change entire file path". However Alteryx is considering the whole file path and I am getting error "path provided is not valid". ex. "C:/user../odb:Provider=SQLOLEDB.1;Password=__EncPwd1__;Persist Security Info=True;User ID=sa;Initial Catalog=XXX;Data Source=YYY;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ZZZZ;Use Encryption for Data=False;Tag with column collation when possible=False;". 

It is adding my system path to the SQL path.

I am using Dynamic Input tool in Batch macro, as multiple tables needs to be exported and those table names I am populating in excel file based on user

In a nutshell, I want to export multiple tables from a SQL database, however my tables as well as my database as well as my SQL server can change based on user inputs.

Is there a way to solve this issue?

[apathetichell]

Do you have different connection names for your different SQL Server names? Assuming you do - and assuming you have set them up in In-DB (I assume there are a finite number of potential SQL server options here) - I would use Dynamic Connect In-DB - which would allow you to specify both the connection and the query dynamically and easily as both would be passed to the Tool as (the only two) fields...  Note - DCIDB only takes one record by design. For multiple SQL pulls you will need to use a batch macro.

[SimranMukhija]

Hi,

Were you able to find a work around for this?