Location of the base code for Alteryx WFs which is based on MongoDB

Question

While working on a Migration project, I have come across a scenario where the original connection needs to be modified to another connection in all the WFs (on Alteryx server). What is the best and quick process to get it done so we don't have to modify each and every WF (there are 800+ WFs.) The Server is based on user managed MongoDB. Which table/collection of MongoDB is the Alteryx WF source code for all the WFs saved on? So that I can change the data source there and it will be reflected globally in all the WFs on the Alteryx server.  Thanks in advance for your help!

fmvizcaino · Answer

That is very well described, @KGT. I totally forgot about the DCM connections 😅

KGT · Answer

What you are trying to do seems simple on the surface, but depends a lot on how they were set up. Naturally, connection info often has encryption etc and so there are different points that you will hit where an ID has to match to another ID from another section, and those other sections are not always easy to find. This changes depending on the Connection type as well.

First thing to check!!!! How are the connections set up? DCM/Server Data Connections/Aliases/Embedded in workflows?

Hopefully, however it is set up on your server, will allow you to just change them via API: https://help.alteryx.com/20241/en/server/api-overview/alteryx-server-api-v3/server-api-v3-objects.html#server-api-v3-objects

However, not knowing the age of your server not sure what was implemented. But with it being over 800 workflows, I imagine it's a few years old, hence, DCM may not be set up, and so it depends whether alias' or Server Data Connections were utilised as to how easy this could be. It may actually be that doing a server upgrade and re-config might be the easiest solution (and that's not light).

Is it just the connection string that needs to change, and no credentials etc? If so, then you may have a chance, but not sure if you're after the workflows, or the dataConnections table. Everything in the dataConnections collection should be accessible from the Gallery UI.

* If the workflow connections were set up with Aliases, then you don't have to do anything, just change the alias on the server
* If the workflow was set up with DCM, then if the Driver is staying the same, you should be able to just change it in DCM. (Might be different if changing from Oracle to Teradata for instance).
* If they all have independent connections, then you'll touch a lot more, but you'll be working with XML (inside zips, possibly split between collections) * If it's directly in the XML, that you're looking for, naturally there's some hashing and encryption you'll have to deal with. Workflows are stored in Mongo as yxzp, which are a zip of the workflow and whatever else they need. These can be extracted by API and then unpacked, however loading them back will give them different Workflow ID's and so break all connections.
* If the workflows just need credential changes, then I advise setting up DCM and changing to that. 
* If they need the connection string and credentials changed, then again, I advise setting up a centralised management (like DCM or Data Connections on server) and then working on transferring to that. (The actual method might be difficult)

The other thing that you might want to look at is if you can find a migration workflow that talks about changing connection properties. 5-10 years ago, there was a common problem with setups (when legacy connections from pre-2017ish were not centralised) that people were trying to manually fix, by migrating workflows from Dev to Prod and changing the connections on the way. There would be some community posts about it (they will probably use APIv1), but not sure where... might be a couple hours of research and testing...

fmvizcaino · Answer

It was the first thing I mentioned in my answer.

AlteryxGallery database, dataconnections collection.

https://help.alteryx.com/20241/en/server/configure/database-management/mongodb-management/mongodb-schema-reference/alteryxgallery-mongodb-schema.html#idp411931

The connection string is encripted so you won't be able to modify them.