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

[andrewfrankwilson]

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.

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

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

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.

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.

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

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:

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".

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.

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.  

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

[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 · Accepted solution]

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.

[EricWe]

Hi @andrewfrankwilson, 

I am glad the issue is resolved. For others that may encounter this problem, the linked article in the previous post provides details about how to use In-DB connection files from a shared drive. The Password Encryption option Hide is needed. This method prevents the password encryption from being machine-specific. It ensures the encrypted password for the connection is accessible from the Gallery.