Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Ignore ODBC Data Input Errors within Union

Csand
8 - Asteroid

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

 

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.

 

3 REPLIES 3
JoeS
Alteryx
Alteryx

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.

Csand
8 - Asteroid

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

 

WF.PNGBatchMacro.PNG

JoeS
Alteryx
Alteryx

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!

Labels