Export (0) Print
Expand All

Selecting Input Columns Programmatically

After you have programmatically connected components, select the columns from upstream components that you will transform or pass through to downstream components. If you do not select input columns for your component, the component does not receive any rows from the data flow task.

Call the GetVirtualInput method to retrieve the list of available columns from an upstream component, then call the SetUsageType method of the design-time component instance to select columns from the virtual input column collection. When you call this method, the component creates a new input column in its input column collection that has the same lineage ID as the corresponding column in the output collection of the upstream component.

Do not select columns by calling the SetUsageType method of the virtual input object directly, because this bypasses the component's ability to reject columns based on inappropriate data types or other properties.

The following code sample demonstrates how to use the design-time instance of a component to select the columns for a component.

using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

namespace Microsoft.SqlServer.Dts.Samples
{
  class Program
  {
    static void Main(string[] args)
    {
      // Create a package and add a Data Flow task.
      Package package = new Package();
      Executable e = package.Executables.Add("STOCK:PipelineTask");
      TaskHost thMainPipe = e as TaskHost;
      MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;

      // Add an OLE DB connection manager to the package.
      ConnectionManager conMgr = package.Connections.Add("OLEDB");
      conMgr.ConnectionString = "Provider=SQLOLEDB.1;" +
        "Data Source=<servername>;Initial Catalog=AdventureWorks2008R2;" +
        "Integrated Security=SSPI;";
      conMgr.Name = "SSIS Connection Manager for OLE DB";
      conMgr.Description = "OLE DB connection to the AdventureWorks2008R2 database.";

      // Create and configure an OLE DB source component.  
      IDTSComponentMetaData100 source =
        dataFlowTask.ComponentMetaDataCollection.New();
      source.ComponentClassID = "DTSAdapter.OleDbSource";
      // Create the design-time instance of the source.
      CManagedComponentWrapper srcDesignTime = source.Instantiate();
      // The ProvideComponentProperties method creates a default output.
      srcDesignTime.ProvideComponentProperties();
      // Assign the connection manager.
      source.RuntimeConnectionCollection[0].ConnectionManager =
        DtsConvert.GetExtendedInterface(conMgr);
      // Set the custom properties of the source.
      srcDesignTime.SetComponentProperty("AccessMode", 2);
      srcDesignTime.SetComponentProperty("SqlCommand",
        "Select * from Production.Product");

      // Connect to the data source,
      //  and then update the metadata for the source.
      srcDesignTime.AcquireConnections(null);
      srcDesignTime.ReinitializeMetaData();
      srcDesignTime.ReleaseConnections();

      // Create and configure an OLE DB destination.
      IDTSComponentMetaData100 destination =
        dataFlowTask.ComponentMetaDataCollection.New();
      destination.ComponentClassID = "DTSAdapter.OleDbDestination";
      // Create the design-time instance of the destination.
      CManagedComponentWrapper destDesignTime = destination.Instantiate();
      // The ProvideComponentProperties method creates a default input.
      destDesignTime.ProvideComponentProperties();

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

      // Get the destination's default input and virtual input.
      IDTSInput100 input = destination.InputCollection[0];
      IDTSVirtualInput100 vInput = input.GetVirtualInput();

      // Iterate through the virtual input column collection.
      foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
      {
        // Call the SetUsageType method of the destination
        //  to add each available virtual input column as an input column.
        destDesignTime.SetUsageType(
           input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
      }

      // Verify that the columns have been added to the input.
      foreach (IDTSInputColumn100 inputColumn in destination.InputCollection[0].InputColumnCollection)
        Console.WriteLine(inputColumn.Name);
      Console.Read();

      // Add other components to the data flow and connect them.
    }
  }
}
Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft