CODEWITHSUNDEEP
sql-server-2008ssis
Dayton Brown
Dayton Brown

Reputation: 1240

SSIS For Each File Enumerator multiple file filters

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

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

That or a cool workaround would be great.

Thanks,

Upvotes: 2

Views: 7584

Answers (4)

Joost
Joost

Reputation: 1913

What about a foreach loop with regex support? http://microsoft-ssis.blogspot.com/2012/04/custom-ssis-component-foreach-file.html

enter image description here

Upvotes: 2

Valentino Vranken
Valentino Vranken

Reputation: 5815

Another option would be something that I prefer to call a "subpackage". In your case, the package would contain just one ForEach loop, configured to loop over the FileSpec as configured in a package variable. The package itself would receive the value for the FileSpec variable through Parent Package Configuration.

That way you have a generic package that does what you expect it to do, available to be called from any other package. To process files with two different filters, all that you need to do is call the package twice, each time with a different value for the variable.

If you're not in favor of using Parent Package configs, that can be avoided by calling the package using an Execute Process task that calls dtexec.exe, while the value for the parameter is passed through the Arguments property.

Upvotes: 0

Raj More
Raj More

Reputation: 48016

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.

Upvotes: 2

Registered User
Registered User

Reputation: 8395

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?

Upvotes: 0

Related Questions