Dynamic IN-DB causes IN-DB Union Tool Gallery fail (All inputs must have same connection)

Alteryx Designer Version 2019.4.8.22007

Alteryx Server Version 2019.4

Purpose

I needed to create a workflow that would dynamically update an In-DB WHERE clause depending on the date the workflow was run (Not included in example screenshots). This was achieved using a text input tool that contains two fields 1) the Connection String and 2) the Query string. These are fed into the Dynamic Input In-DB Tool which queries an Oracle database.

Text Input Local Path.PNG

The results of that query are to be In-DB Unioned with Data Streamed In-DB that have the same columns and data types.

Designer Workflow.PNG

Both the Dynamic Input In-DB Tool and the In-DB Data Stream In connections are both done by a File Connection Type.

Data Stream In-DB Local Path.PNG

When that file path is used in the connection name fields for both tools in Designer, the In-DB Union tool does not throw an error.

Designer Messages.PNG

Problem

However, when publishing to Gallery, the connection file is packaged into the workflow as an external file (i.e. "File:_externals\1\IN-DBConnect.indbc"). Trying to run the workflow in Gallery without also updating the Text Input Connection (which is still a path to the file on a local drive) will cause a predictable failure.

Gallery Initial Run.PNG

So the text input tool needs to be adjusted to the new packaged connection file location:

Text Input External File Path.PNG

This now matches the connection name for the other tools (e.g. Data Stream In Tool) in the workflow after it has been saved to Gallery:

Data Stream In-DB External File Path.PNG

It is use of the packaged file that is necessary for Gallery, that appears to be causing the In-DB Union tool to Error: "Union In-DB: All inputs must have the same In-DB connection".

Gallery External File Run.PNG

The packaged file also causes failure in Designer, but I can get the workflow to execute if I revert the connections back to the file path on my local machine.

Designer Workflow Union Fail.PNG

Designer Messages Union Fail.PNG

I have verified that the workflow packaged external connection files are still connecting to the database by using the Browse In-DB tools. Both the Data Stream In and the Dynamic Input In-DB tools are successfully connecting to the Oracle Database and returning the expected values; but when it comes time to In-DB Union that data, their shared connection is not recognized as the same by the In-DB Union tool.

Dynamic Input Browse.PNG

Does anyone have any advice on this? Is the In-DB Union tool a known problem in Designer/Gallery?

Workflow

Gallery

Connectors

Server

Bug

In Database

Database Connection

In-Database

Accepted answers

andrewfrankwilson

Hi Eric,

Yes, I do realize that problem with the path in the text-input tool; It was detailed in my description, and addressed. The problem is not the connection file, which works both locally and as the gallery packaged asset. I have since moved on from using a Gallery packaged connection file to using a connection file hosted on a shared drive that Alteryx can access. When this approach is taken, the In-DB Union tool error I have described no longer occurs.

Thanks for your suggestion on another approach for a dynamic In-DB query on date.

All comments

EricWe

Hi @andrewfrankwilson,

When saving the workflow to Gallery, try clicking on Workflow Options, Manage workflow assets, and verify if the connection file is checked as a workflow dependency. If checked, then a copy is saved to the Gallery’s database along with the workflow. Afterward, when running the workflow, the Data Stream In Tool references the database copy, while the Text Input Tool has the location of the external copy of the connection file.

To avoid this issue, use the method described in this article: Guide to In-DB Connection Files.

Another option is using a Date Time Now Tool followed by a Data Stream In Tool. This method would allow Designer to provide the current date and stream it into the In-DB portion of the workflow with no separate connection needed for that information. Afterward, you could use an In-DB Formula tool to build the SQL with the current date field for use in the Dynamic Input In-DB Tool.

andrewfrankwilson

Hi Eric,

Thanks for your suggestion on another approach for a dynamic In-DB query on date.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335