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.

thursdaysgeek · Accepted Answer · 2011-03-30 18:01:29Z

37

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)

answered Mar 30, 2011 at 18:01

thursdaysgeek

7,99621 gold badges82 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Loki70 Over a year ago

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.

thursdaysgeek Over a year ago

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

Collectives™ on Stack Overflow

SSIS Foreach Loop Folder as variable

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related