Selecionando colunas de entrada programaticamente

Selecionando colunas

Chame o método GetVirtualInput para recuperar a lista de colunas disponíveis de um componente upstream, e chame o método SetUsageType da instância do componente de tempo de design para selecionar colunas da coleção de colunas de entrada virtual. Quando você chama esse método, o componente cria uma nova coluna de entrada em sua coleção de colunas de entrada com a mesma ID de linhagem da coluna correspondente da coleção de saída do componente upstream.

Não selecione colunas chamando o método SetUsageType do objeto de entrada virtual diretamente, pois ele ignora a capacidade do componente de rejeitar colunas com base em tipos de dados inadequados ou outras propriedades.

Exemplo

O exemplo de código seguinte mostra como usar a instância de tempo de design de um componente para selecionar as colunas para um componente.

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.
    }
  }
}

Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Module Module1

  Sub Main()

    ' Create a package and add a Data Flow task.
    Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _
      New Microsoft.SqlServer.Dts.Runtime.Package()
    Dim e As Executable = package.Executables.Add("STOCK:PipelineTask")
    Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _
      CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)
    Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

    ' Add an OLE DB connection manager to the package.
    Dim conMgr As ConnectionManager = 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.  
    Dim source As IDTSComponentMetaData100 = _
      dataFlowTask.ComponentMetaDataCollection.New
    source.ComponentClassID = "DTSAdapter.OleDbSource"
    ' Create the design-time instance of the source.
    Dim srcDesignTime As CManagedComponentWrapper = 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(Nothing)
    srcDesignTime.ReinitializeMetaData()
    srcDesignTime.ReleaseConnections()

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

    ' Create the path from source to destination.
    Dim path As IDTSPath100 = dataFlowTask.PathCollection.New
    path.AttachPathAndPropagateNotifications(source.OutputCollection(0), _
      destination.InputCollection(0))

    ' Get the destination's default input and virtual input.
    Dim input As IDTSInput100 = destination.InputCollection(0)
    Dim vInput As IDTSVirtualInput100 = input.GetVirtualInput

    ' Iterate through the virtual input column collection.
    For Each vColumn As IDTSVirtualInputColumn100 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)
    Next

    ' Verify that the columns have been added to the input.
    For Each inputColumn As IDTSInputColumn100 In destination.InputCollection(0).InputColumnCollection
      Console.WriteLine(inputColumn.Name)
    Next
    Console.Read()

    ' Add other components to the data flow and connect them.

  End Sub

End Module

Consulte também

Conceitos