SSIS: How to parameterise foreach folder loop location from project.params value?

user_6088227 0
Viewed 954

I have an SSIS Project I want to parameterise to be able to have dev, staging, production environments.

I'm starting slowly, and I'd like to parameterise some hardcoded filepaths.

I have a foreach loop container that iterates over a directory and performs some actions on each of the files it contains.

In Visual Studio I right click and click edit. There are four tabs: General, Collection, Variable Mappings, and Expressions.

Under "Collection", there is a variable Folder. This is hardcoded currently. I'd like it to be at least a package parameter, or preferable, a project parameter followed by a suffix.

Once I've added a project parameter, how can I do this?

For this purpose, let's say I have a variable: SuperFolderLocation.

Would I change the variable Folder's destination to be @[$Project::SuperFolderLocation] + "\subdirectory"? Do I need to use expressions to calculate the variable Folder's destination? If so, what is the variable I need to set? And can you just plop a variable in the Folder destination and it will interpret it?

ssis
1 Answers

First thing you need to do is create the project parameter:

enter image description here

Then in the foreach loop container select collection and make sure that "Foreach File Enumerator" is selected in the drop down. Click on Expressions and select "Directory" in the Property dropdown. Click on Expression ellipsis and this will open the Expression Builder Then select your parameter from System Variables and complete the expression by adding the subfolder. You can also click on button Evaluate Expression to make sure the path is correctly evaluated.

enter image description here

user_29649630
Related