I followed this example from my colleague Marnix Wisselaar to create a SQL Iterator.
Iterating rows of a table (Record-by-record - SQL Iterator) in a workflow | Community (ataccama.com)
I created a SQL Iterator task that runs this query:
select mp_name , mp_gid , rule_name , table_name from SNOW.hlp_DQ_findings where valid_to is NULL
And it runs another workflow, that runs a component that does no more than writing the values of these columns in the iterator table and write the results to a text file.
But for some reason my SQL Iterator workflow kept failing with this message:
Task 'Run DQC' : failed with exception: Model is not valid.
So I removed bits and parts to find out what the issue was. And I found out that if I left out just one of the 4 columns, the rule_name, the SQL Iterator and subsequent workflow and component ran just fine.
I could not understand why only adding that column resulted into issues. So I decided to rebuild the entire workflow and component structure from scratch. Just to see if it would work better this time. It had the exact same issue with the same column.
Then I had a better look at the contents of my iterator table, specifically the rule_name column. And it looked like this:
INITIALS "Erven/Boedel" and DATE OF DEATH empty
My colleague Marnix suggested to remove the double quotes and other special characters. And then the error message no longer occurred.
The lessons:
- This was one heck of a unit test 😂
- "Model is invalid” messages can be vague, but you might want to have a look at the data you are iterating over.
- Best to go easy on quotes and double quotes in your SQL iterator table.
