Using SQL Server 2005 Integration Services : Programming Integration Services (part 3) - Setting Up Column Information

Setting Up Column Information

The source table for this example is HumanResources. Employee in the AdventureWorks database. We set up the OLE DB source adapter to use two columns from that table as its data source:

// Tell the OLE DB Source adapter to use the SQL Command access mode.
oledbSourceComponent.SetComponentProperty("AccessMode", 2);

// Set up the SQL command
oledbSourceComponent.SetComponentProperty("SqlCommand", "select EmployeeID, Title from
HumanResources.Employee");

					  

Best Practices

We only need two columns from the table, so it is more effcient to bring only the two columns from the database into the data flow instead of using a SQL command such as select * from HumanResources.Employee, which would bring all the columns into the data flow.

The OLE DB source adapter can use one of several access modes to get its data. The mode we are using here is SqlCommand, which has an AccessMode enumeration value of 2. This access mode tells the adapter to execute a SQL statement to retrieve its data.

More Info

Other AccessMode values are available, such as a mode for retrieving the SQL command from an Integration Services variable. One way to get a list of access modes and their associated enumeration is to look in the type library for the OLE DB source adapter. For example, you can open Oledbsrc.dll in the Object Browser in Visual Studio.

Both AccessMode and SqlCommand are custom properties on the OLE DB source adapter, which means they are specific to the adapter and are added to its component metadata object when ProvideComponentProperties is called.

Best Practices

You might wonder why we call a method to set a property when we can just access the property directly in the component metadata’s custom property collection. For example, we could have set the AccessMode property by using oledbSourceMetadata. CustomPropertyCollection[“AccessMode’].value = 2;.

The reason is that when we use SetComponentProperty, we ask the component to update its component metadata for us, giving it a chance to reject or react to the new property value if it needs to. If a design-time method is available for modifying the component metadata, you should use it instead of working directly with the component metadata.

// Set up the connection manager object
// This needs a reference to Microsoft.SqlServer.DTSRuntimeWrap
runtimeConnectionSource.ConnectionManager =
DtsConvert.ToConnectionManager90(connectionManagerSource);

To retrieve the column information for the SQL command, the adapter must have access to the connection manager for its connection. You set this by assigning the connection manager to the ConnectionManager property on the adapter’s runtime connection. Notice the use of DtsConvert, which is needed to convert the connection manager from the ConnectionManager to IDTSConnectionManager90. As mentioned at the beginning of this sample, for this code to compile, we’ll need to add a reference to Microsoft.SqlServer.DTSRuntimeWrap, which is needed for IDTSConnectionManager90 to be available.

Now that the connection manager is set up, we can establish the database connection and tell the component to set up its columns:

// Establish the database connection
oledbSourceComponent.AcquireConnections(null);

// Set up the column metadata
oledbSourceComponent.ReinitializeMetaData();

// Release the database connection
oledbSourceComponent.ReleaseConnections();

// Release the connection manager
runtimeConnectionSource.ReleaseConnectionManager();

The call to AcquireConnections tells the OLE DB source adapter to use the connection manager to establish access to the database. This is followed by the call to ReinitializeMetaData, which tells the adapter to query the database for the columns associated with the SQL command provided earlier, and then modify its component metadata to capture those columns. One of these modifications is the addition of output columns on the component metadata, which are needed for the data flow engine to construct its data buffers for execution. Another modification is the addition of external metadata columns, which are columns that describe metadata external to the component—in this case, the columns returned by the SQL command. At this point, the external metadata and output columns are the same, both describing the columns returned by the SQL command. However, the OLE DB source adapter allows its output columns to be customized by the user later but doesn’t allow its external metadata columns to be customized. This means the adapter can always rely on its external metadata columns to be a snapshot of the columns returned by the SQL command if it needs to know anything about those columns. After the call to ReinitializeMetaData, the connection and connection manager are released.

More Info

We have described how the OLE DB source adapter uses its external metadata columns and how it implements ReinitializeMetaData, but note that these are specific to that adapter; another component can use its external metadata columns for any purpose and have whatever logic is necessary in its ReinitializeMetaData.

Figure 1 shows the use of the Advanced Editor to access a component’s external metadata and output column information.

Figure 1. Using the Advanced Editor to see a component’s external metadata and output columns

More Info

What happens if we change the OLE DB source adapter’s SQL command and call ReinitializeMetaData again? Some components might just delete all the output columns based on the old SQL command and create new output columns based on the new SQL command. However, the OLE DB source adapter tries to reuse as many of the old columns as possible. So why is it important for the adapter to try to avoid deleting and re-creating output columns? The reason is to minimize disruption to the components downstream of the adapter that have already established mappings to those output columns for their input columns.

Setting Up the OLE DB Destination Adapter

Now that we have the OLE DB source adapter set up to retrieve data into the data flow, we need to set up an OLE DB destination adapter to take the data out of the data flow and insert it into a destination table. Setting up the OLE DB destination adapter mostly follows the same process as setting up the source adapter, so we will skip a line-by-line analysis. However, one difference that needs explanation is the following:

// Set up the external metadata columns
oledbDestinationComponent.ReinitializeMetaData();

Here the OLE DB destination adapter creates only a snapshot of its destination using external metadata columns—it does not create any input columns. One reason for this is that you create input columns by specifying mappings from them to upstream output columns from where the data originates, and those output columns might not yet be available.

More Info

Input columns map to upstream output columns using a property called LineageID.