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 -
SORName | Name | Value | SOR_DEST |
Dev | servername | desti_server_dev | Dest |
empsor | username | dinesh_dev | Sor |
empsor | password | dinesh0 | Sor |
empsor | servername | dev_emp_server | Sor |
QA environment -
SORName | Name | Value | SOR_DEST |
QA | servername | desti_server_qa | Dest |
empsor | username | dinesh_qa | Sor |
empsor | password | dinesh0 | Sor |
empsor | servername | qa_emp_server | Sor |
PROD environment -
SORName | Name | Value | SOR_DEST |
PROD | servername | desti_server_prod | Dest |
empsor | username | dinesh_prod | Sor |
empsor | password | dinesh0 | Sor |
empsor | servername | prod_emp_server | Sor |
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.
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
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
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
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.
Environment | Servername |
DEV | Dest_server_dev |
SE | Dest_server_se |
QA | Dest_server_qa |
PROD | Dest_server_prod |
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!