Change Connection String in a Gallery Connection

Question

Hello,

We recently went through a migration between SQL Server instances (2008 to 2016). On the day that we cut over I expected to be able to change the connection string of the Gallery Connection to point to the new server. However 3 day later we had found that it hadn't actually made the changes at the workbook level on scheduled jobs. Am I misunderstanding how this is supposed to work?

I will also note that every time we publish jobs, we remove the gallery connection assets (for a totally different reason), as I understand it removing this dependency means that it will always call to the gallery? Do I have this backwards? If I am correct, what I imagine might be happening is even though we remove that dependency on the gallery, it still saves the dependency on the scheduled job? Or did I really mess something else up?

CristianoJ · Answer

Hello @Treyson,

It was wrong from my part to assume you were using ODBC connections.  You should be fine with using your ODBC connections too.

As long as you update the connection string inside the Gallery connection all jobs that call that same connection should run fine too.

Make sure you Sync the connections on your server's Designer > Options > Advanced Options > Manage Data Connections

Treyson · Answer

Hey @CristianoJ

I had about 75 jobs that I had to go through and put a new gallery connection in so that work is done.

But to respond to your suggestions. 99% of these jobs had gallery connections, we manage all of the really important stuff through a service account so as part of our peer review/scheduling process, I move everything to gallery connections. The ODBC connection comment has me thinking... We are using OLE connections because they seem to be faster and more reliable. Could this be an issue?

I will probably end up testing this suggestion below with a demo workbook. But for clarification, I am misunderstanding how this is supposed to function? Should I be able to adjust the gallery connection strings and that is passed through to the jobs below? Because I can stop thinking that no problem, I just need to know lol.

CristianoJ · Answer

Hello,

I don't know how many schedules you have but if you do not have a lot it might be easier if you setup the schedules again.

Are you sure that all the scheduled jobs are using Gallery Shared Connections?

Verify that your connection string is updated in your ODBC System DSN connections too, as it could be that some workflows could be using a System DSN connection instead of a Gallery Connection.

Another thing worth doing would be to backup and delete the files:

SystemAlias.xml;

SystemConnections.xml;

GalleryAlias.xml;

UserConnections.xml

Those files can be found in the following locations:

C:\Users\<username>\AppData\Roaming\Alteryx\Engine

C:\ProgramData\Alteryx\Engine