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:

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.