SSIS – Programmatically connect DataFlow components


In this part of my series of blogs about dynamically modifying SSIS Packages I describe how to use the SSIS programming API to create and connect data flow components.

DataFlow Components have input or/and output column collections depending on the type of component. Components like DataSources do not have an input collection whilst destination component do not have a destination collection.

If you already have built your data flow you can examine the components columns and how they are connected to other components by querying the components input and output column collection.

As you may know data flow components are interconnected through paths. A path describes the flow of data from the sources through transformations to the destinations. Programmatically a path is represented by a IDTSPath100 object. Unless a component isn’t connected by a path it is not possible to add any columns to its input column collection. So the first step after creating the data flow components is to interconnect them with paths.

Here is an example how you can connect two components with an IDTSPath100 object:

// Create the source component. 
IDTSComponentMetaData100 sourceComponent =
dataFlowTask.ComponentMetaDataCollection.New();
sourceComponent.ComponentClassID = "DTSAdapter.OleDbSource";
CManagedComponentWrapper srcDesignTime = source.Instantiate();
srcDesignTime.ProvideComponentProperties();

 

// Create the destination component.
IDTSComponentMetaData100 destination =
dataFlowTask.ComponentMetaDataCollection.New();
destination.ComponentClassID = "DTSAdapter.OleDbDestination";
CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();

 

// Create the path.
IDTSPath100 path = dataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(source.OutputCollection[0],
destination.InputCollection[0]);

To retrieve the possible input columns for a component, one can use the GetVirtualInput method of the InputColumnCollection object.

IDTSInput100 input = destination.InputCollection[0];
IDTSVirtualInput100 virtualInput = input.GetVirtualInput();
 

The virtual input column collection contains all the possible columns from an upstream component.

This example code shows how to iterate over all input columns and connect them to the destination component.

      foreach (IDTSVirtualInputColumn100 column in virtualInput.VirtualInputColumnCollection)
      {
        destDesignTime.SetUsageType(
           input.ID, virtualInput, column.LineageID, DTSUsageType.UT_READONLY);
      }


Skip to main content