title: SSIS: Executing package with custom component
body: <p>I have created a package that aims to achieve a high level of flexibility, allowing mappings from a flat file to a custom component to be determined at runtime. I have done so by doing the following:</p>
  
  <ol>
  <li><p>Created a package which fetches column mappings from a database table. This table contains the "flat file source columns" and the "database table columns". This data is stored in a package variable. </p></li>
  <li><p>I created a custom pipeline component (destination adapter) that processes the rows from the flat file and then populates a data table with the data from the flat file into the "database table columns". This data table is stored in a variable. (This custom component was not added to the package through the designer)</p></li>
  <li><p>In order to achieve "dynamic mapping", I created a script task which ran after my initial SQL query which fetched the column mappings. The script task does the following:</p>
  
  <ul>
  <li>Creates a package</li>
  <li>Adds a Data Flow task</li>
  <li>Adds a flat file source (and connections) to the Data Flow Task</li>
  <li>Adds my custom component </li>
  <li>Maps the flat file source to my custom component</li>
  <li>Passes in my "Parent package variables" and executes the package</li>
  <li>Retrieves the "Data table variable" from the newly created package</li>
  </ul></li>
  </ol>
  
  <p>I then run another script task which runs insert statements for each record in the data table variable. </p>
  
  <p>The issue that I am having is that my package will not execute unless I add a flat file destination to the package that I created programatically. </p>
  
  <p>I discovered this by saving the package that I created programatically and then added the flat file destination through the designer. I then opened a script task, loaded this package and executed it and it ran.</p>
  
  <p>Below is the code for the creating the child package programatically:</p>
  
  <pre><code> 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 &gt; 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 &gt; 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 &lt; 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
  </code></pre>
  
  <p>Adding the destination flat file component has allowed my package to execute, however it does not make sense as my custom component type is a <strong>destination adapter</strong>. The additional destination flat file component is inefficient and I would like to know what the need for this is.</p>