topic Ignore ODBC Data Input Errors within Union in Alteryx Designer Desktop Discussions

Ignore ODBC Data Input Errors within Union

Csand — Thu, 27 Jun 2019 03:46:25 GMT

I have a workflow with many inputs from ODBC sources, and sometimes one (or more) fails. All of these data inputs are consolidated via a Union, but any errors result in the Union failing because no metadata is received from the stream(s) with errors. I do want the workflow to proceed even if one of the inputs has an error. Does anyone have a solution for this?

This is similar, but different since my first tool is a data input vs. a directory search (which is designed to handle files that don't exist, not connection problems).

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Ignoring-Missing-Data-Connection-for-Union-Tool/td-p/147943

A few things i've tried without luck (but hopefully might help get you thinking!)

- Adding a select after each input with the metadata configured,

- Adding a filter after each input which excludes null rows.

- Adding a generate row after each input to add a garbage row.

- Adding a macro after each input with the input optional.

- Adding a dynamic input after each input (there may be some way to accomplish this based on the above solution, but I couldn't figure it out)

- Configured the input tool SQL query with a union to a static value row, but fails since one part of the SQL-based union fails.

Re: Ignore ODBC Data Input Errors within Union

JoeS — Thu, 27 Jun 2019 10:35:29 GMT

This made me think a little!

Inherently Alteryx is going to treat a connection error to a database as an issue, as it's your input that's gone wrong.

What I have done is created a batch macro that enables you to put in the Select statement, and if I create a select statement of "gibberish" it still returns the other rows.

So the batch macro idea should work for you.

I have attached mine, but you may need to modify it further for your needs.

Re: Ignore ODBC Data Input Errors within Union

Csand — Thu, 27 Jun 2019 12:59:27 GMT

Thank you @JoeS . The solution didn't perfectly capture it, since I am using different databases rather than different queries. However, the solution was still the same since I just had to send the databases to the batch macro rather than the queries. I'm including my final example below.

* Note that this assumes you are using Windows authentication for MSSQL databases. If you want to use this for non-integrated authentication, you can either send the passwords to the batch macro or use a common password that is hashed by Alteryx in the input tool (so doesn't need to be sent to the batch macro).

Re: Ignore ODBC Data Input Errors within Union

JoeS — Thu, 27 Jun 2019 13:24:41 GMT

Great stuff, I wasn't quite sure what element you needed to switch around and guessed the wrong one.

Glad you managed to alter it and it's working for you!