Using SQL Server 2005 Integration Services : Programming Integration Services (part 2)

Adding a Data Flow to the Package

The first step in working with a data flow after creating the package programmatically is to add a data flow task:

// Create a new package
Package package = new Package();
package.Name = "OLE DB Transfer";

// Add a Data Flow task.
TaskHost taskHost = package.Executables.Add("DTS.Pipeline") as TaskHost; taskHost.Name =
 "Transfer Table";

					  

Use DTS.Pipeline as the moniker for the data flow task when you add a new task to the package. It’s good practice to give a name to the data flow if your package is opened in the Integration Services designer. If you don’t, the designer labels the data flow task with its GUID, which is not very user friendly.

IDTSPipeline90 pipeline = taskHost.InnerObject as MainPipe;

IDTSPipeline90 is the top-level interface that provides access to the entire data flow object model. It is retrieved as the inner object from the task host for the data flow task.

Setting Up Components and Component Metadata

After adding a data flow task and getting the pipeline object, you can start adding members to the pipeline object’s various collections. Let’s start by adding an OLE DB source adapter. Looking at the collections available, you see that there is no component collection, but there is a component metadata collection. The component metadata contains all the metadata that describes a component—for example, the columns and properties used by the component. From the component metadata, the component itself can be instantiated. Within this instantiated component is the custom code that does the work based on how the component metadata is set up. For example, for the OLE DB source adapter, its component metadata holds the SQL command as a property and its component contains the OLE DB client code that uses that property to retrieve data into the data flow.

// Get the pipeline's component metadata collection
IDTSComponentMetaDataCollection90 componentMetadataCollection =
pipeline.ComponentMetaDataCollection;

// Add a new component metadata object to the data flow
IDTSComponentMetaData90 oledbSourceMetadata = componentMetadataCollection.New();

// Associate the component metadata object with the OLE DB Source Adapter
oledbSourceMetadata.ComponentClassID = "DTSAdapter.OLEDBSource";

					  

You associate the component metadata with a data flow component by setting its ComponentClassID property. For unmanaged components, which are COM objects, the class ID or prog ID is used. For managed components, the full assembly name is used.

Tip

One way to determine what value to use for the ComponentClassID property for a component is to look in its property page after placing it onto the data flow design surface. The value needed to instantiate the component is shown under the ComponentClassID property if it’s unmanaged and under the UserComponentTypeName if it’s managed. This brings up an interesting point: if we are looking at the UserComponentTypeName property for managed components, what value is in its ComponentClassID property? It is the class ID for the managed component wrapper, which is an unmanaged component internal to the data flow that hosts the managed component. One of the main reasons for the use of a managed component wrapper is to marshal the data in the buffers from the unmanaged data flow execution engine to managed components.

// Instantiate the OLE DB Source adapter
IDTSDesigntimeComponent90 oledbSourceComponent = oledbSourceMetadata.Instantiate();

					  

Calling the component metadata’s Instantiate method instantiates the component that contains all the design-time and runtime custom code. The component has two interfaces: IDTSDesigntimeComponent90 and IDTSRuntimeComponent90. As you can guess, the design-time interface contains methods for design-time functionality, such as setting properties and adding columns to its component metadata, and the runtime interface contains methods related to running the component in a data flow execution. To programmatically set up the data flow, we only need to use the methods on the design-time interface.

// Ask the component to set up its component metadata object
oledbSourceComponent.ProvideComponentProperties();

The ProvideComponentProperties method is usually the first method in the component that is called after a component is instantiated. This method tells the component to add any necessary properties, inputs, outputs, and other metadata needed to set up its component metadata from an empty state to a state that describes the component. In our sample, before we call ProvideComponentProperties, the component metadata will not have any metadata specific to the OLE DB source adapter. When the method is called, the component adds metadata, such as the SqlCommand and CommandTimeout properties, to the component metadata to make it specific to the OLE DB source adapter.

Setting Up Connection Managers

Some components use connection managers to access resources outside of the data flow. The OLE DB source adapter is one such component, and the connection manager it uses is the OLE DB Connection Manager. Components describe their association with connection managers by using a runtime connection collection in their component metadata. There is one runtime connection in that collection for each connection manager required by the component. We have already called ProvideComponentProperties on the OLE DB source adapter, which should have added one runtime connection to that collection because that adapter requires only one connection to do its work.

// Add an OLE DB connection manager
ConnectionManager connectionManagerSource = package.Connections.Add("OLEDB");
connectionManagerSource.Name = "OLEDBSource";

// Set the connection string
connectionManagerSource.ConnectionString = "provider=sqlncli;server=.;integrated
security=sspi;database=adventureworks";

					  

As the data source for this example, we’ll use a table in the AdventureWorks database on the local machine, logging on using Windows authentication. You can change the connection string as needed, depending on your own configuration.

// Set up the OLE DB Source adapter's runtime connection
IDTSRuntimeConnection90 runtimeConnectionSource =
oledbSourceMetadata.RuntimeConnectionCollection["OleDbConnection"];

runtimeConnectionSource.ConnectionManagerID = connectionManagerSource.ID;

The runtime connection is associated with the connection manager by the connection manager’s ID. When a connection manager is made available to the component by the client or the data flow engine, the ID in the ConnectionManagerID property is used to determine which connection manager is needed.

Tip

One way to find the name of the runtime connection is to look for it in the Connection Managers page in the component’s Advanced Editor in the designer.

More Info

Instead of using OleDbConnection as the index value into a component’s runtime connection collection, we could also use 0 as the index because there is only one runtime connection in the collection. However, in general, if there is more than one object in a collection, using the object name instead of an integer as the index is less error-prone because the data flow does not guarantee the ordering of objects in its collections.