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

Expressions: Part III

Jamie's blog got me thinking about expressions and wanted to give some background and a little more information about how they work.

Background

Property expressions came relatively late in the development cycle. There was always an idea floating around on our team about putting expressions on properties. Even though it seems obvious now, it wasn't obvious to us early on how to do it. In fact, we visited the idea on several occasions, never coming to a decent approach or design. Very early on, we didn't even have the notion of an expression evaluator in the runtime at all. Even though we were writing one for the derived column and conditional split, we didn't make the conceptual leap as soon as we might have that we could use the expression evaluator for the runtime as well. Eventually, Sergei Ivanov and I were sitting in my office trying to solve the long time problem, how to replace property mappings with a more workable and manageable solution. We knew we wanted to use expressions somehow. By this time, we'd incorporated them into the variables, on the forloop and into precedence constraints. The problem was, how to do it in a way that would successfully apply the result at the right time. Sergei said something about applying them at execution time and then we realized we were on to something.

How They Work

The trick is “applying“ the result of the expressions at the right time. For tasks, that meant right before initialization, validation, and execution. For other objects, the key edge events are different. Here's when expressions are applied.

• Tasks
• Before Saving
• After Loading
• Before initialization
• Before Validation
• Before Execution
• Connection Managers
• Before Saving
• After Loading
• Before initialization
• Before returning from AcquireConnection calls
• Log Providers
• Before Saving
• After Loading
• Before Initialization
• ForEach Enumerators
• Before Saving
• After Loading
• Before Initialization
• Before returning from GetEnumerator calls

These object types are “hosted“. That means there is a host that is, for the most part, transparent in the designer that creates the component and wraps it. The hosts do a lot of stuff for the components so the components don't have to do it themselves. This factors out functionality to a centralized place and eliminates the need for custom component writers to re-implement all that code. Property expressions are one of those features implemented on the hosts. The host examines the components when it loads it the first time and discovers things like what properties the component exposes etc. It also tracks the property expressions that you create on the component and “applies“ the expression to the property or in other words, evaluates the expression and sets the corresponding property for the expression to the resulting value.

Hopefully, this helps you better understand the behavior of property expressions. As shown in previous blogs, property expressions are an extremely powerful way to dynamically modify package behavior based on various package external and internal parameters.

Hope this helps,

Thanks,

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

Next CTP...

As the next CTP is pending, I thought I'd highlight some of the newer “stuff“ you'll be seeing in that release:

• A “Log Events“ window in the designer. Will show you certain log events in the designer without setting up a log provider.
• New user interfaces for the Union All and Merge transforms.
• Excel connection manager, source and destination adapters
• ForEachADOEnumerator will access individual columns
• Import/Export Wizard will nativel support Excel
• Many, many bug fixes.
• SSIS Server now in the SQL Computer Manager

Yay, even at the gates...

K