SSIS:如何对接受输入源列(RowKey)的派生新列(EmpID)进行编程生成？

Question

源输入列- Empid(int)

目标列- RowKey(int)

将我的代码下垂，但这会导致错误“验证错误。

DataFlowTask派生列2:尝试查找错误代码0xC0010009失败的名为"Empid“的输入列。在输入列集合中找不到指定的输入列。“

我的密码是-

  TaskHost TKHSQLHost = (TaskHost)exe;
  TKHSQLHost.Name = "Dataflow Task";
  MainPipe dataFlowTask = (MainPipe)TKHSQLHost.InnerObject;
  // Create the source component.
  IDTSComponentMetaData100 source =dataFlowTask.ComponentMetaDataCollection.New();
  source.ComponentClassID = "DTSAdapter.OleDbSource";
  CManagedComponentWrapper srcDesignTime = source.Instantiate();
  srcDesignTime.ProvideComponentProperties();
  // Assign the connection manager.
  if (source.RuntimeConnectionCollection.Count > 0)
  {
     source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(ConnMgrAdvent);
     source.RuntimeConnectionCollection[0].ConnectionManagerID =pkg.Connections["EmpCon"].ID;
   }
  // Set the custom properties of the source.
  srcDesignTime.SetComponentProperty("AccessMode", 0);
  srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Employee]");
  // Connect to the data source, and then update the metadata for the source.
  srcDesignTime.AcquireConnections(null);
  srcDesignTime.ReinitializeMetaData();
  srcDesignTime.ReleaseConnections();
  // Create the destination component.
  IDTSComponentMetaData100 destination =dataFlowTask.ComponentMetaDataCollection.New();
  destination.ComponentClassID = "DTSAdapter.OleDbDestination";
  CManagedComponentWrapper destDesignTime = destination.Instantiate();
  destDesignTime.ProvideComponentProperties();
  // Assign the connection manager.
  destination.RuntimeConnectionCollection[0].ConnectionManager =DtsConvert.GetExtendedInterface(ConnMgrImport_DB);
  if (destination.RuntimeConnectionCollection.Count > 0)
   {
         destination.RuntimeConnectionCollection[0].ConnectionManager =DtsConvert.GetExtendedInterface(ConnMgrImport_DB);
         destination.RuntimeConnectionCollection[0].ConnectionManagerID =pkg.Connections["stgEmpCon"].ID;
    }
    // Set the custom properties of the destination
     destDesignTime.SetComponentProperty("AccessMode", 0);
     destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[emp12]");
    // Connect to the data source, and then update the metadata for the source.
    destDesignTime.AcquireConnections(null);
    destDesignTime.ReinitializeMetaData();
    destDesignTime.ReleaseConnections();   
    //Derived Column
     IDTSComponentMetaData100 derived =dataFlowTask.ComponentMetaDataCollection.New();
     derived.Name = "Derived Column Component";
     derived.ComponentClassID = "DTSTransform.DerivedColumn.3";
     CManagedComponentWrapper DesignDerivedColumns = derived.Instantiate();
     DesignDerivedColumns.ProvideComponentProperties();        //design time
     derived.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
     derived.InputCollection[0].HasSideEffects = false;
     //update the metadata for the derived columns
     DesignDerivedColumns.AcquireConnections(null);
     DesignDerivedColumns.ReinitializeMetaData();
     DesignDerivedColumns.ReleaseConnections();
     //Create the path from source to derived columns 
     IDTSPath100 SourceToDerivedPath = dataFlowTask.PathCollection.New(); 
   SourceToDerivedPath.AttachPathAndPropagateNotifications(source.OutputCollection[0], derived.InputCollection[0]);
      //Create the path from derived to desitination
      IDTSPath100 DerivedToDestinationPath = dataFlowTask.PathCollection.New();            DerivedToDestinationPath.AttachPathAndPropagateNotifications(derived.OutputCollection[0], destination.InputCollection[0]);                    
      **IDTSOutputColumn100 myCol = derived.OutputCollection[0].OutputColumnCollection.New();
      myCol.Name = "RowKey";               myCol.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4,0 , 0, 0,0);
      myCol.ExternalMetadataColumnID = 0;
      myCol.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
      myCol.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
      IDTSCustomProperty100 myProp = myCol.CustomPropertyCollection.New();
      myProp.Name = "Expression";        
      myProp.Value = "Empid";         
      myProp = myCol.CustomPropertyCollection.New();**strong text**
      myProp.Name = "FriendlyExpression";
      myProp.Value = "Empid";**
      IDTSInput100 dInput = derived.InputCollection[0];
      IDTSVirtualInput100 vdInput = dInput.GetVirtualInput();//Get this components default input and virtual input                           
      //Create the input columns for the transformation component 
      IDTSInput100 input = derived.InputCollection[0];
      IDTSVirtualInput100 derivedInputVirtual = input.GetVirtualInput();           
      input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
      input.ErrorOrTruncationOperation = "";
      DesignDerivedColumns.ReleaseConnections();
      // Get the destination's default input and virtual input.
      IDTSInput100 destinationinput = destination.InputCollection[0];
      int destinationInputID = input.ID;
      IDTSVirtualInput100 vdestinationinput = destinationinput.GetVirtualInput();
      //Iterate through the virtual input column collection.
      foreach (IDTSVirtualInputColumn100 vColumn in vdestinationinput.VirtualInputColumnCollection)
       {
           IDTSInputColumn100 vCol = destDesignTime.SetUsageType(destinationinput.ID, vdestinationinput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
           String cinputColumnName = vColumn.Name;
           var columnExist = (from item in destinationinput.ExternalMetadataColumnCollection.Cast<IDTSExternalMetadataColumn100>()
                                   where item.Name == cinputColumnName
                                   select item).Count();
                if (columnExist > 0)
                    destDesignTime.MapInputColumn(destinationinput.ID, vCol.ID, destinationinput.ExternalMetadataColumnCollection[vColumn.Name].ID);
       }                
    app.SaveToXml(@"D:\TestEmp.dtsx", pkg, null);  

Answer 1

我将以下内容添加到派生列到设计时properties.Then中，使其工作良好。

    IDTSInput100 DerivedColumnInput = derived.InputCollection[0];
    IDTSVirtualInput100 DerivedColumnVirtualInput = DerivedColumnInput.GetVirtualInput();
    IDTSVirtualInputColumnCollection100 DerivedColumnVirtualInputColumns = DerivedColumnVirtualInput.VirtualInputColumnCollection;

    // Added the below to validate input columns
    foreach (IDTSVirtualInputColumn100 virtualInputColumnDT in DerivedColumnVirtualInputColumns)
    {
        // Select column, and retain new input column
        if (virtualInputColumnDT.Name=="EmpID")
        {

            DesignDerivedColumns.SetUsageType(DerivedColumnInput.ID, DerivedColumnVirtualInput, virtualInputColumnDT.LineageID, DTSUsageType.UT_READONLY);

        }

    }