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).
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.
Solved! Go to Solution.
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.
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).
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!