Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Selection of connection string based on environment

Dinesh13
6 - Meteoroid

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_DEST
Devservernamedesti_server_devDest
empsorusername dinesh_devSor
empsorpassworddinesh0Sor
empsorservernamedev_emp_serverSor

 

QA  environment -

SORNameNameValueSOR_DEST
QAservernamedesti_server_qaDest
empsorusername dinesh_qaSor
empsorpassworddinesh0Sor
empsorservernameqa_emp_serverSor

 

PROD environment - 

SORNameNameValueSOR_DEST
PRODservernamedesti_server_prodDest
empsorusername dinesh_prodSor
empsorpassworddinesh0Sor
empsorservernameprod_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.

5 REPLIES 5
afv2688
16 - Nebula
16 - Nebula

Hello @Dinesh13 ,

 

If you want for the worfklow to be smart enough you need to give him something to know it has changed enviroment. Is there a way for him to interpret on which system it is currently?

 

If yes, there could be a possibility to arrange some way, if not we would need to think of something else.

 

Regards

Dinesh13
6 - Meteoroid

Thanks. Actually, i have created a 3 collections - DEV, SE  & QA. In that, i have added my workflows. If my worflow run under DEV, then it should connect to DEV DB and get data. If my work run under QA then it should connect QA and run. But am not sure how to get can i get this and also not sure is it possible to provide such details from server team.

 

It would be nice if you can tell me best approach. May be we can store DEV, SE & QA connection strings in variables in workflow. For now, i can hardcode some variable say "Environment". If I give ''DEV'' value to this variable then i should get data from DEV server. If i give "QA" then i should get data from QA. 

 

I'm connecting SOR & Destination using ole db drivers.

 

Destination

Dev=odb:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False; Intial Catalog=Employee;Data source = DEV,1001; Use Encryption for Data=False; Tag with column collation when possible=False|||Alteryx.Employee
SE=odb:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False; Intial Catalog=Employee;Data source = SE,1001; Use Encryption for Data=False; Tag with column collation when possible=False|||Alteryx.Employee
QA=odb:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False; Intial Catalog=Employee;Data source = QA,1001; Use Encryption for Data=False; Tag with column collation when possible=False|||Alteryx.Employee

afv2688
16 - Nebula
16 - Nebula

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

Dinesh13
6 - Meteoroid

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.

 

EnvironmentServername
DEVDest_server_dev
SEDest_server_se
QADest_server_qa
PRODDest_server_prod
p-g
8 - Asteroid

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!

Labels