Kirk Haselden

Next CTP : Variable mappings in the ForEachLoop...

There were some problems with the way early incarnations of the ForEach Loop passed around the current enumerated value from its configured ForEachEnumerator. For example, there are some ForEachEnumerators that return more than one value per each iteration and there was only one system variable for holding the current enumerated value.

Although variable mappings in the ForEachLoop aren't new, in the next CTP ForEachPropertyMappings have been removed completely. If you were using them, you may need to convert your packages to using variable mappings.

Here's how:

Here are some different combinations and how to upgrade.

Current Enumerated Value mapped to a Variable

• If you already have the variable, and you mapping to the Value property, just select the existing variable in the “Variable“ column. Done.

Current Enumerated Value mapped to a Property on a Task

• Create a new variable in the “Variable“ column in the ForEach Loop UI.
• The variable can be at any scope.
• Close the ForEachLoop and select the task that has the property to which you mapped.
• Click on the Expressions property in the property grid.
• Click on the button with “...“ on it.
• That opens the Property Expressions Editor.
• Select the property you wish to modify in the “Property“ column.
• Click on the “...“ button in the expression column.
• That opens an expression editor.
• Drag the variable you created above from the top left pane into the expression window.
• You're done.
• Now the property on the task will have the value the ForEachLoop puts into the variable that you mapped.

Current Enumerated Value mapped to a Connection String (or other property) on a Connection Manager

This is the same as above for tasks, except the variable must be created at package scope because those are the only variables visible to connection managers.

Current Enumerated Value mapped to a Log Provider property

This is also the same as for tasks, except, to get at the expressions you need to view the log provider in the package explorer view.

Most folks, I believe, are no longer using ForEachProperty Mappings, but if you are I hope this is clear and helps you if you work through any problems. 

Thanks,

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden