SSIS Foreach Loop Folder as variable

Question

I need to set the Folder in my Foreach loop to a variable location. I tried just adding the location to my package configuration, and it acted like it worked, but when I opened the configuration file, it wasn't there.

I had it set to Foreach File Enumerator, with the path hardcoded, and Retrieve fully qualified file name. My variable Mappings have a variable CurrentFile with index 0.

So, I've changed it to a Foreach From Variable Enumerator, with a SourceFiles variable of \server\path*.*. But now it's complaining about that CurrentFile, that the type of value being assigned differs from the variable type (string).

I'm guessing that I need to somehow use the Expressions with the Enumerator on the Collections tab? How?

• General: Foreach Loop Container
• Collection: Foreach From Varialbe Enumerator; Variable is User::SourcePath; Expressions (not used)
• Variable Mappings: Variable User::CurrentFile, Index 0
• Expressions: (not used)

Error: 0xC001F009 at MyPackage: The type of the value being assigned to variable "User::CurrentFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 1 to variable "User::CurrentFile" cannot be applied.

Invalidity

Answer 1

A co-worker showed me how to get it to work. Do NOT change it to "Foreach from Variable Enumerator", but leave it as "Foreach File Enumerator".

• Collection: Foreach File Enumerator; put in a folder and files in the Enumerator configuration. Under Expressions, set the Directory to a user variable pointing to the directory (it will take precedence to the folder you've entered); set the FileSpec to a user variable pointing to the files (which will take precendence to the Files you've entered).

Directory @[User::SourcePath]  (value: \\server\directory)
FileSpec @[User::CopyFileName] (value: *.*)

• Variable Mappings: Variable User::Current File, index 0
• Expressions: (not used)

Invalidity

Just a minor clarification to this great solution. There are several places to change the Expressions values on a ForEach Loop. Make sure to use the one on the Collection tab, directly below the Enumerator field, as it is the only place this "Directory" value exists. – Atomizer

Over 10 years later, at a different job, and this helped me! – Invalidity