SSIS:使用自定义组件执行包

Question

我创建了一个旨在实现高度灵活性的包，允许在运行时确定从平面文件到自定义组件的映射。我这样做的方式如下：

1. 创建一个包，该包从数据库表中获取列映射。此表包含“平面文件源列”和“数据库表列”。此数据存储在包变量中。
2. 我创建了一个自定义管道组件(目标适配器)，它处理平面文件中的行，然后将来自平面文件的数据填充到“数据库表列”中。此数据表存储在变量中。(此自定义组件未通过设计器添加到包中)
3. 为了实现“动态映射”，我创建了一个脚本任务，它运行在获取列映射的初始SQL查询之后。脚本任务执行以下操作：

- Creates a package
- Adds a Data Flow task
- Adds a flat file source (and connections) to the Data Flow Task
- Adds my custom component 
- Maps the flat file source to my custom component
- Passes in my "Parent package variables" and executes the package
- Retrieves the "Data table variable" from the newly created package

​

然后运行另一个脚本任务，为数据表变量中的每个记录运行insert语句。

我遇到的问题是，除非我将一个平面文件目的地添加到我通过编程创建的包中，否则我的包将不会执行。

通过保存编程创建的包，然后通过设计器添加平面文件目的地，我发现了这一点。然后我打开一个脚本任务，加载这个包并执行它，然后它运行。

下面是以编程方式创建子包的代码：

 Try
  'CREATE PACKAGE
  package = New Microsoft.SqlServer.Dts.Runtime.Package()

  '-----------------------
  'CREATE DATA FLOW TASK
  '-----------------------
  Dim PipelineTask As Executable = package.Executables.Add("STOCK:PipelineTask")
  Dim DataFlowTask_Runtime As Dts.Runtime.TaskHost = CType(PipelineTask, Dts.Runtime.TaskHost)
  DataFlowTask_Runtime.Name = "MyCustom Data Flow Task"

  Dim DataFlowTask_DesignTime As MainPipe = CType(DataFlowTask_Runtime.InnerObject, MainPipe)

  '------------------
  'SETUP CONNECTIONS
  '------------------
  'CREATE FLAT FILE CONNECTION
  Dim FFConnectionManager As ConnectionManager = package.Connections.Add("FLATFILE")
  FFConnectionManager.ConnectionString = FileName
  FFConnectionManager.Name = "Flat File Source Connection"

  'SETUP FLAT FILE COLUMNS AND DELIMETERS
  GetColumnsFromFlatFile(FFConnectionManager, DelimeterType.CarriageReturnLineFeed, DelimeterType.LineFeed, DelimeterType.Tab, 0)

  '-----------------------
  'CREATE SOURCE COMPONENT
  '-----------------------
  ' Set up the souce component of the Dataflow task
  Dim FFSourceComponent As IDTSComponentMetaData100 = DataFlowTask_DesignTime.ComponentMetaDataCollection.New
  FFSourceComponent.Name = "Flat File Source"

  ' The managed source is an instantiation of the source.
  FFSourceComponent.ComponentClassID = "{D23FD76B-F51D-420F-BBCB-19CBF6AC1AB4}"
  Dim FFSourceComponent_DesignTime As Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapper = FFSourceComponent.Instantiate
  FFSourceComponent_DesignTime.ProvideComponentProperties()

  ' Link the connection to the source component.
  If FFSourceComponent.RuntimeConnectionCollection.Count > 0 Then
    FFSourceComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(FFConnectionManager)
    FFSourceComponent.RuntimeConnectionCollection(0).ConnectionManagerID = FFConnectionManager.ID
  End If
  FFSourceComponent_DesignTime.AcquireConnections(Nothing)
  FFSourceComponent_DesignTime.ReinitializeMetaData()


  '------------------------------------------
  'Create Output Mappings from the flat file.
  '------------------------------------------
  Dim OutputColumnCollection As IDTSOutputColumnCollection100
  OutputColumnCollection = FFSourceComponent.OutputCollection(0).OutputColumnCollection

  Dim exOutColumn As IDTSExternalMetadataColumn100
  For Each OutputColumn As IDTSOutputColumn100 In OutputColumnCollection
    exOutColumn = FFSourceComponent.OutputCollection(0).ExternalMetadataColumnCollection(OutputColumn.Name)
    FFSourceComponent_DesignTime.MapOutputColumn(FFSourceComponent.OutputCollection(0).ID, OutputColumn.ID, exOutColumn.ID, True)
    'Add the output column name and ID to an array so that we can identify them at a later stage
    outputColumnLineageIDs.Add(OutputColumn.Name, OutputColumn.ID)
  Next
  FFSourceComponent_DesignTime.ReleaseConnections()

  '----------------------------
  'CREATE MYCUSTOM SCRIPT COMPONENT
  '----------------------------

  Dim MyCustomScriptComponent As IDTSComponentMetaData100 = DataFlowTask_DesignTime.ComponentMetaDataCollection.New
  MyCustomScriptComponent.ComponentClassID = "MyCustomBuffer.CustomComponents.MyCustomScriptComponent, MyCustomBuffer, Version=1.0.0.0, Culture=neutral, PublicKeyToken=516f7fc6acf0e6e0"
  MyCustomScriptComponent.Name = "MyCustom Script"

  Dim MyCustomScriptComponent_DesignTime As CManagedComponentWrapper = MyCustomScriptComponent.Instantiate
  MyCustomScriptComponent_DesignTime.ProvideComponentProperties()

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

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

  MyCustomScriptComponent_DesignTime.ReinitializeMetaData()

  ' Iterate through the virtual input column collection.
  For Each vColumn As IDTSVirtualInputColumn100 In vInputMyCustom.VirtualInputColumnCollection
    ' Call the SetUsageType method of the destination
    '  to add each available virtual input column as an input column.
    Dim InputCol As IDTSInputColumn100 = MyCustomScriptComponent_DesignTime.SetUsageType(InputMyCustom.ID, vInputMyCustom, vColumn.LineageID, DTSUsageType.UT_READONLY)
  Next

  '----------------------------
  'CREATE FLAT FILE DESTINATION
  '----------------------------

  Dim DestFFConnManager As ConnectionManager = package.Connections.Add("FLATFILE")
  DestFFConnManager.ConnectionString = "C:\Test\MyCustomBufferTest.txt"
  DestFFConnManager.Name = "Flat File Destination Connection"

  Dim DestFFConnManager_DesignTime As IDTSConnectionManagerFlatFile100 = DestFFConnManager.InnerObject
  DestFFConnManager_DesignTime.ColumnNamesInFirstDataRow = True
  DestFFConnManager_DesignTime.CodePage = 1252
  DestFFConnManager_DesignTime.HeaderRowDelimiter = vbTab
  DestFFConnManager_DesignTime.Format = "Delimited"
  DestFFConnManager_DesignTime.HeaderRowsToSkip = 0
  'FlatFileConnection.TextQualifier = ""
  DestFFConnManager_DesignTime.RowDelimiter = vbTab

  'CREATE FLAT FILE DESTINATION
  Dim FFDestination As IDTSComponentMetaData100 = DataFlowTask_DesignTime.ComponentMetaDataCollection.New
  FFDestination.ComponentClassID = "{8DA75FED-1B7C-407D-B2AD-2B24209CCCA4}"
  FFDestination.Name = "Flat File Destination"

  Dim FFDestination_DesignTime As CManagedComponentWrapper = FFDestination.Instantiate
  FFDestination_DesignTime.ProvideComponentProperties()

  ' Link the connection to the source component.
  If FFDestination.RuntimeConnectionCollection.Count > 0 Then
    FFDestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(DestFFConnManager)
    FFDestination.RuntimeConnectionCollection(0).ConnectionManagerID = DestFFConnManager.ID
  End If
  Dim Destpath As IDTSPath100 = DataFlowTask_DesignTime.PathCollection.[New]
  Destpath.AttachPathAndPropagateNotifications(MyCustomScriptComponent.OutputCollection(0), FFDestination.InputCollection(0))

  Dim DestInput As IDTSInput100 = FFDestination.InputCollection(0)
  Dim VDestinput As IDTSVirtualInput100 = DestInput.GetVirtualInput()
  Dim VDestInputColCollection As IDTSVirtualInputColumnCollection100 = VDestinput.VirtualInputColumnCollection


  Dim indexMax As Integer = VDestInputColCollection.Count - 1
  For index As Integer = 0 To indexMax
    ' Get input column to replicate in flat file
    Dim virtualInputColumn As IDTSVirtualInputColumn100 = VDestInputColCollection(index)

    ' Add column to Flat File connection manager
    Dim flatFileColumn As IDTSConnectionManagerFlatFileColumn100 = TryCast(DestFFConnManager_DesignTime.Columns.Add(), IDTSConnectionManagerFlatFileColumn100)
    flatFileColumn.ColumnType = "Delimited"
    flatFileColumn.ColumnWidth = virtualInputColumn.Length
    flatFileColumn.DataPrecision = virtualInputColumn.Precision
    flatFileColumn.DataScale = virtualInputColumn.Scale
    flatFileColumn.DataType = virtualInputColumn.DataType
    Dim columnName As IDTSName100 = TryCast(flatFileColumn, IDTSName100)
    columnName.Name = virtualInputColumn.Name

    If index < indexMax Then
      flatFileColumn.ColumnDelimiter = vbTab
    Else
      flatFileColumn.ColumnDelimiter = vbCrLf
    End If
  Next

  FFDestination_DesignTime.AcquireConnections(Nothing)
  FFDestination_DesignTime.ReinitializeMetaData()

  For Each virtualInputColumn As IDTSVirtualInputColumn100 In VDestInputColCollection
    ' Select column, and retain new input column
    Dim inputColumn As IDTSInputColumn100 = FFDestination_DesignTime.SetUsageType(DestInput.ID, VDestinput, virtualInputColumn.LineageID, DTSUsageType.UT_READONLY)
    ' Find external column by name
    Dim externalColumn As IDTSExternalMetadataColumn100 = DestInput.ExternalMetadataColumnCollection(inputColumn.Name)
    ' Map input column to external column
    FFDestination_DesignTime.MapInputColumn(DestInput.ID, inputColumn.ID, externalColumn.ID)
  Next

Catch ex As Exception
  MsgBox(ex.Message)
End Try

添加目标平面文件组件允许执行我的包，但是它没有意义，因为我的自定义组件类型是目标适配器。附加的目标平面文件组件效率低下，我想知道这是什么需要。

Answer 1

您可能在验证自定义组件时得到了错误，您应该做的是：

将自定义组件中的延迟验证属性设置为true。