Data Flow Properties

You build a Data Flow Task by adding pipeline components, setting their property values, and creating paths between them. So the behavior of a given data flow is really determined by the collection of subcomponents it contains. But, there are some important properties that the Data Flow Task itself exposes of which you should be aware. You can see them by right-clicking on the Data Flow designer tab and selecting the Properties menu option. The following sections describe each of the important data flow properties. The Taskhost properties are covered in other sections of the book, so they aren't revisited here. The following properties are confined to the Misc section in the property grid.

Expressionable Component Properties

Depending on the types of components you have in the data flow, different properties are exposed in the property grid. For example, Figure 19.1 shows the designer with the properties for a Data Flow Task.

Figure 19.1. Data Flow Task properties

At the top of the Misc properties list, three properties are listed, starting with [Derived Column], [OLE DB Command], and [OLE DB Command 1]. These properties are not really properties of the Data Flow Task, but rather properties of a component in the Data Flow Task that have been advertised as "expressionable" properties. Figure 19.2 shows the Property Expressions Editor for the Data Flow Task in Figure 19.1.

Figure 19.2. Data Flow Task properties

Notice the same properties visible in the drop down. Although the dialog box displays the properties as though they were data flow properties, adding an expression for the property directly affects the property by that name on the component. Figure 19.2 shows how the names are constructed as well. The Derived Column transform is capable of having multiple outputs with properties on each of those outputs. Figure 19.3 shows the Advanced Editor for Derived Column component shown in Figure 19.1.

Figure 19.3. The Derived Column FriendlyExpression property is expressionable

So, the property that is exposed on the package,

[Derived Column].[Derived Column Output].[Derived Column 1].[FriendlyExpression] 

is really the path to the FriendlyExpression property on the Derived Column 1 output in the Derived Column Output outputs collection on the Derived Column component.

Because the OLE DB Command components are simpler and the SqlCommand property is a property found directly on the OLE DB Command component, the property is represented as a simple, two-part path. If you remove the OLE DB Command and Derived Column components from the data flow, their properties are also removed from the properties grid.

Storage Paths

Two properties specify temporary storage locations. The BLOBTempStoragePath tells the Data Flow Task what file system folder it should use when storing Binary Large Objects (BLOBs). The BufferTempStoragePath tells the Data Flow Task where to store spooled buffers. The BLOBTempStoragePath is always used anytime there are BLOBs in the data. The Data Flow Task only uses the BufferTempStoragePath when it runs out of memory and must spool buffers out to disk.

Buffer Size Properties

These are properties that affect the size of the buffer.

• DefaultMaxBufferRows This is the maximum number of rows in a buffer. The default is 10,000.

• DefaultBufferSize This is the maximum size of the buffer in bytes and defaults to 10MB. The minimum is currently 64KB; the maximum is 100MB. Neither the minimum or maximum sizes can be changed in this version.

Engine Threads

This is a hint to the engine for how many worker threads to create. It can be modified to create more threads in the engine thread pool and the number of threads the engine uses is twice the number specified.

RunInOptimizedMode

This is the last of the data flowspecific properties and tells the Data Flow Task to attempt to trim unused components. The data flow engine trims components by simply ignoring them. You might experience some performance gains from this setting because it eliminates components that have no side effects.