Selection of connection string based on environment

Question

Hi, I'm new to Alteryx. I have a requirement to run workflow in DEV, QA & PROD and move data from oracle(source) to MS SQL(destination). I want to get collection name and if collection name='qa' then connection string details data should be picked from config table from 'qa' db. If collection name='prod' then data should be picked from config table from 'prod'dB. Right now, I do not have provision to get collection name. So I will declare a variable='qa' then it should pick 'qa' dB(select * from config where sorname='Dev', whatever variable value(Dev/qa/prod) data should come from that Database. I have a configuration table in 'employee' DB in MS SQL server. I have below data in dbo.config, which is in MS SQL.

My source SOR - Oracle ; My Destination - MS SQL

Dev environment -

SORNameNameValueSOR_DESTDevservernamedesti_server_devDestempsorusername dinesh_devSorempsorpassworddinesh0Sorempsorservernamedev_emp_serverSor

QA  environment -

SORNameNameValueSOR_DESTQAservernamedesti_server_qaDestempsorusername dinesh_qaSorempsorpassworddinesh0Sorempsorservernameqa_emp_serverSor

PROD environment -

SORNameNameValueSOR_DESTPRODservernamedesti_server_prodDestempsorusername dinesh_prodSorempsorpassworddinesh0Sorempsorservernameprod_emp_serverSor

I want to build my workflow based on above data. If i move workflow from one environment to other then workflow should be smart enough automatically pull data from dbo.config table. I will not change any values on run time. May be am wrong in picking values, u can correct me. Any help is really appreciate.

p-g · Answer

Hi @Dinesh13 ,

Can you look into the solutions/workarounds suggested by @chris_love  or @revathi  in the below links?

Environment dependent connections - Alteryx Community

Database connections in lower and higher environme... - Alteryx Community

Hope this helps!

Dinesh13 · Answer

OK. I'm new to Alteryx. Could you please help me in providing solution file. I have added input tool with below 2 columns. I'm thinking to use Engine.WorkflowDirectory - I think this will give my workflow directory. If this doesn't work, would request you to suggest best solution.

EnvironmentServernameDEVDest_server_devSEDest_server_seQADest_server_qaPRODDest_server_prod

afv2688 · Answer

Hello @Dinesh13 ,

as far as I know there is no actual way right now to retrieve that information from the gallery into the workflow. You can get some information from the MongoDB database but would only show what there is, not what you are using right now.

Maybe a good way would be to manually add a variable to the workflow like you suggested.

Regards