Data Flow Property Expressions

Although property expressions can be added to virtually all writable properties of containers, connection managers, log providers, Foreach Enumerators, and tasks, property expressions can only be added to a limited set of properties on certain data flow components. This feature was added very late and so it is limited to only a few components.

Table 9.1 lists the data flow components and their properties that can have expressions.

Table 9.1. Expressionable Data Flow Components

Component	Property	Description
XML Source	XMLData	Dynamically modifies the data processed
XML Source	XMLSchemaDefinition	Dynamically changes to reflect the XMLData
Fuzzy Grouping	MaxMemoryUsage	Allows flexibility for different load scenarios
Fuzzy Lookup	MaxMemoryUsage	Allows flexibility for different load scenarios
OLEDB Command	SqlCommand	Enables dynamically changing the dataset returned
PercentSampling	SamplingValue	Allows percent sampled to change or be turned off (set to 0)
Row Sampling	SamplingValue	Allows percent sampled to change or be turned off (set to 0)
Sort	MaximumThreads	Allows you to tune sort for varying loads, and so on
Unpivot	PivotKeyValue	Changes pivot results
Pivot	PivotKeyValue	Changes pivot results
Flat File Destination	Header	Allows flexible header modifications
SS Mobile Dest	TableName	Easily configures destination table to target
SQL Dest	Timeout	Allows you to change the timeout and is useful for debugging when server may not be available
Data Reader Source	SqlCommand	Dynamically modifies the data returned
Derived Column	Friendly Expression	Dynamically modifies the expression used to create data for column

Adding Data Flow Property Expressions

Data flow property expressions are handled the same as other tasks, but there is a twist. Because the Data Flow Task contains subcomponents, the properties on subcomponents are exposed as if they are properties of the Data Flow Task. The property name for subcomponents is exposed in the following form:

[Component Name].[Input|Output Name].[Column Name].[Property]

• [Component Name] This is the name you see in the component when you look at the component in the data flow designer.

• [Input|Output Name] This is the name of the input or output on which the column is found.

• [Column Name] This is the column on which the property is found.

• [Property] This is the property associated with the expression.

The derived column component happens to be one of the components that supports expressions on one of its properties. If you were to view the properties for the Data Flow Task, you would see a property similar to the one shown in Figure 9.5.

Figure 9.5. The four-part name for data flow component properties is shown in the Property Expressions Editor

The four-part name is composed of the following elements:

• [Build New ID] The name of the component

• [Derived Column Input] The input name

• [Product ID] The column ID

• [Friendly Expression] The property name

Property Expressions in the Pipeline

Late in the Yukon development cycle, "property expressions in the pipeline" was a phrase that often caused grief and acrimony on the Integration Services team. In the beta 2 time frame, property expressions were quickly becoming an important feature in the runtime. We were beginning to see their power and real benefits. Adding property expressions to data flow components was fraught with technical challenges. Twice we attempted to find a design that would work in the data flow environment and failed. Each attempt resulted in long, painful, and sometimes contentious meetings with no satisfactory conclusions or workable designs. As we got closer and closer to beta 3 (June CTP), there were several bugs and customer requests that might easily be resolved if we just had "property expressions in the pipeline." One day, I approached Matt David, the technical lead for the Data Flow Task and started a discussion around the problem. Matt said, "I've been thinking about this a bit" or something similar. Sergei Ivanov walked over and said, "Did I hear someone say 'property expressions in the pipeline?'" Matt proposed his solution, and Sergei suggested options for how to make it work without modifying the user interface. This was very important at this time because we were, after all, trying to stabilize for beta 3. There was a bit of magic in the air that day. As we talked, Cim Ryan, the test lead for the Data Flow Task happened by and overheard someone say "property expressions in the pipeline" and hovered around the office, just to see what the crazy developers were up to. Before long, half the development team and many of the testers were sitting in Matt's office working out the details of how to make it work. Finally, we had worked out the issues and struck on a design that met all the requirements. A little talk with others on the team to get approval and "property expressions in the pipeline" became the last major feature added to Integration Services 2005.