Alteryx designer Discussions

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

Data Output Tool - dynamically change server connection details

Highlighted
8 - Asteroid

I am trying to use the Data Output tool to write results to one of 3 MS SQL databases. Each database has the same table names & structures, but they are on 3 different servers with different connection details.

 

Should I be able to put the connection string in a field in the data and then use the "Take File/Table Name Of Field" control on the Data Output tool to direct the data to the correct server like this? I can't seem to get it working, I get the error message

 

Error: Output Data (674): Error opening connect string: Microsoft OLE DB Provider for SQL Server: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.\08001 = 17

 

I know the connection strings I am using are correct, if I give them to the Data Output tool in the "Write to File or Database" field and don't use "Take File/Table Name Of Field", they work fine.

 

EDIT: To explain the situation further, the 3 different databases are for Development, Test and Production environments. I have access to 3 Alteryx servers for DEV/TEST/PROD and 3 corresponding MS SQL database servers. The DEV Alteryx server must connect only to the DEV MS SQL database and so on. Connecting to the wrong database in any environment results in an error. The flow is able to tell which server environment it is running on by looking at the User Name (GetEnvironmentVariable("USERNAME")).

 

Highlighted
Alteryx
Alteryx

Hi @PaulRB 

 

I like to do things like that with a macro. 

 

I'm using a drop down to create a list with the three database options. I am pointing the database in the connection string to a generic 'XXXXX' and am using the update tool to update just that part of the connection string:

 

HenrietteH_0-1583526165259.png

 

Attaching the macro as an example for you as well. 

 

 

Henriette Haigh
Principal Support Engineer -- Knowledge Management Coach
Alteryx, Inc.

Highlighted
8 - Asteroid

Hi @HenrietteH and thanks for the reply!

 

What type of macro would be appropriate?

 

For my flow, there must be no user interaction, the flow using the macro will need to decide which server/environment/connection string to use without intervention. As mentioned, the calling flow can do this using the USERNAME environment variable.

Highlighted
Alteryx
Alteryx

Hi @PaulRB 

 

Apologies, I missed the comment about the environmental variable. 

 

There are a couple of different ways of going about this. 

 

- You could modify the above macro: instead of using the drop down, use a field from the data coming in and populate it with the environmental variable. 

 

- This post suggests using user defined variables. 

 

- While this one uses the dynamic input tool. The connection string is edited in a formula tool based on dynamic criteria and then updated in the tool. 

Henriette Haigh
Principal Support Engineer -- Knowledge Management Coach
Alteryx, Inc.

Highlighted
8 - Asteroid

Thanks again Henriette

 


- You could modify the above macro: instead of using the drop down, use a field from the data coming in

That's a part I cannot figure out. I have made all 3 kinds of macros in the past. I don't think Iterative is appropriate. Batch I'm not sure about for this purpose. For interactive macros, I'm not sure how to get an input field to the macro to be treated like a user input field.

 

- While this one uses the dynamic input tool. The connection string is edited in a formula tool based on dynamic criteria and then updated in the tool. 


I'm familiar with Dynamic Input tool, very useful. Unfortunately I need a Dynamic Output tool!

 

- This post suggests using user defined variables. 

That won't help me because they would still need to be updated by someone between environments, and that's what I'm trying to avoid, because it can get forgotten. HOWEVER, later in that post, Mark also suggests using Aliases. If the Alias could be set up differently on the different server environments, my workflow would not need to care about which environment it is running in. I will contact the server administrators and ask if they can set this up for me.

 

 

Highlighted
Alteryx
Alteryx

Hi @PaulRB 

 

An Alias would be an elegant way of solving the issue and would definitely work. 

I had some time today to go through the macro again and the best way I've found to going about this is with a batch macro.  The control parameter is used to update the database in the output tool and can be mapped to an input field from the workflow. You would need to make sure that only one value is sent to the control parameter by summarizing that (or using a separate input). You could wrap another macro around the batch macro to make sure that the value is always summarized and to hide the "grouping" options. 

 

Overall, I think the Alias would be a nicer solution though!

 

 

Henriette Haigh
Principal Support Engineer -- Knowledge Management Coach
Alteryx, Inc.

Labels