SSIS For Each File Enumerator multiple file types

Question

Hi All,

Is it possible in SQL 2008 (SSIS) to specify multiple file types in the for each loop control?

Something like HH*.* and U*.*.

That or a cool workaround would be great.

Thanks,

Answer 1

I don't think that it is possible to do multiple file types. The only way I know of is to do *.* and conditional logic.

Answer 2

It is possible to specify multiple file extensions. All you need to do is specify in the "Files:" section of the Foreach Loop Editor SampleFileSpec*.* and that will retrieve any files that start with SampleFileSpec regardless of the file type or other trailing characters. You can also create an expression in the FileSpec of the Foreach Loop Editor.

If you need to process multiple known file schemas then you can add multiple data flows in the Foreach Loop Container and set the enabled flag for the data flow based on a conditional statement.

The only advantage I can see to doing this is that you only have to iterate through a folder once with a For Each Loop Container. I would recommend having multiple Foreach Loop Containers with their own dedicated data flows. This would make it easier to maintain the code in the future.

Does this resolve your issue? What use-case are you trying to solve that wouldn't be handled better by separate Foreach Loop Containers?

Answer 3

Use package builder snippets from BI xPress. They have more than 90 different snippets for SSIS including custom looping for files, directory, XML and excel sheets ... http://pragmaticworks.com/products/Business-Intelligence/BIxPress

Cheers!!