Dynamic ETL Flow

Question

Hi

we have a dynamic ETL flow , that uses an sql table for a source of where the file will be and what to do with it

table structure

select Title,
LocationOfFiles,
Filename,
FileType,
SqlTableName,
DateColumns,
IntColumns,
FloatColumns,
Active,
RowColStart,
DatabaseName,
Region,
ID,
Locale

Healix Accuracy,\eu974k16mft01\ukrit\Attachments\Healix,Healix Accuracy,CSV,Alteryx_Testing.Accuracy,"Go To Authorisation Date,Result From Authorisation Date","Source ID,User Id","",Yes,"0,0",Sitel_Reporting,UK,1,

example of info in table

we will have hundreds of records in this table soon - our flow fails if one of the files is not found and we are unsure how to fix this - any suggestions - we have tried a batch macro but cannot get it to configure work correctly

jrlindem · Answer

@rob2003  Yep, that makes sense.  So long as you have that many locations the likelihood of finding nothing and taking your workflow is low.  Glad you figured out a way to make it work.  -Jay

rob2003 · Answer

we are looking at roughly 150 locations and want it fully dynamic- we have found a way though

we search all folder for the files and then match the files to the ones we are looking for - added approx 20 seconds to the flow but now makes it fully dynamic on the ETL

jrlindem · Answer

I've run into this before.  My situation was using a directory import and when there is no file found, it throws an error.  If this is similar to your experience, then a "creative" way to solve this is to have at least one "dummy" file or template file that can always be found in the directory.  Then in your workflow just filter that file out.  This way there's always something to bring in.

Then I use Control Containers + Filter Tool to conditionally check for any relevant files and the workflow runs accordingly.

Let me know if this is in the ballpark of what you're experiencing and if you'd like me to elaborate more.  Hope this helps, -Jay