I have the following script that I use in SSIS to transfer data from a
data file to a SQL table. I want change the code so that I** don’t** need the column names
in the file to be the same as the column names in the SQL tables to which the files are loaded.
It is important to note that the columns of the files and the columns of the SQL tables into which the
data is loaded are dynamic.

I running the script below and it falling because the name of columns in file are different then the columns in SQL table – I want the script to be able to handle different column names between the file and the table

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

#Region "Help:  Using Integration Services variables and parameters in a script"
'To use a variable in this script, first ensure that the variable has been added to 
'either the list contained in the ReadOnlyVariables property or the list contained in 
'the ReadWriteVariables property of this script task, according to whether or not your
'code needs to write to the variable.  To add the variable, save this script, close this instance of
'Visual Studio, and update the ReadOnlyVariables and 
'ReadWriteVariables properties in the Script Transformation Editor window.
'To use a parameter in this script, follow the same steps. Parameters are always read-only.

'Example of reading from a variable:
' startTime = Dts.Variables("System::StartTime").Value

'Example of writing to a variable:
' Dts.Variables("User::myStringVariable").Value = "new value"

'Example of reading from a package parameter:
' batchId = Dts.Variables("$Package::batchId").Value

'Example of reading from a project parameter:
' batchId = Dts.Variables("$Project::batchId").Value

'Example of reading from a sensitive project parameter:
' batchId = Dts.Variables("$Project::batchId").GetSensitiveValue()
#End Region

#Region "Help:  Firing Integration Services events from a script"
'This script task can fire events for logging purposes.

'Example of firing an error event:
' Dts.Events.FireError(18, "Process Values", "Bad value", "", 0)

'Example of firing an information event:
' Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, fireAgain)

'Example of firing a warning event:
' Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0)
#End Region

#Region "Help:  Using Integration Services connection managers in a script"
'Some types of connection managers can be used in this script task.  See the topic 
'"Working with Connection Managers Programatically" for details.

'Example of using an ADO.Net connection manager:
' Dim rawConnection As Object = Dts.Connections("Sales DB").AcquireConnection(Dts.Transaction)
' Dim myADONETConnection As SqlConnection = CType(rawConnection, SqlConnection)
' <Use the connection in some code here, then release the connection>
' Dts.Connections("Sales DB").ReleaseConnection(rawConnection)

'Example of using a File connection manager
' Dim rawConnection As Object = Dts.Connections("Prices.zip").AcquireConnection(Dts.Transaction)
' Dim filePath As String = CType(rawConnection, String)
' <Use the connection in some code here, then release the connection>
' Dts.Connections("Prices.zip").ReleaseConnection(rawConnection)
#End Region

'This method is called when this script task executes in the control flow.
'Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'To open Help, press F1.


Sub Main()

    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)
    thMainPipe.Name = "dataFlowTask"
    Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

    ' Add an TextFile connection manager to the package.  
    Dim cmTextFile As ConnectionManager = package.Connections.Add("FLATFILE")
    cmTextFile.Name = "TextFile Source"
    '        cmTextFile.ConnectionString = "u:test.csv" 'Dts.Variables("User::v_FilePath").Value + Dts.Variables("User::v_FileName").Value
    Dim FullFileName As String = Dts.Variables("User::v_FilePath").Value.ToString + Dts.Variables("User::v_FileName").Value.ToString
    'Dim FullFileName As String = "u:test.csv"
    cmTextFile.ConnectionString = FullFileName
    cmTextFile.Description = "SSIS Connection Manager for Source"
    'cmTextFile.Qualifier = "{CR}{LF}"
    Dim connMgrFlatFileInnerObj As IDTSConnectionManagerFlatFile100 = CType(cmTextFile.InnerObject, IDTSConnectionManagerFlatFile100)
    connMgrFlatFileInnerObj.Format = "Delimited"
    connMgrFlatFileInnerObj.HeaderRowDelimiter = Dts.Variables("User::v_RowDelimiter").Value.ToString ' "{CR}{LF}"
    connMgrFlatFileInnerObj.ColumnNamesInFirstDataRow = True

    Dim stream As StreamReader = New StreamReader(FullFileName) 'Dts.Variables("User::myStringVariable").Value
    'Dim stream As StreamReader = New StreamReader("u:test.csv") 'Dts.Variables("User::myStringVariable").Value

    Dim fieldNames As String = stream.ReadLine()
    Dim columns() As String = fieldNames.Split(",".ToCharArray())
    '        If (fieldNames! = null) Then columns = fieldNames.Split("t".ToCharArray())

    'Headers = GetHeaderArray(FilePath, strSheet)

    ' Configure Columns And their Properties for the Flat File Connection Manager
    Dim indexMax As Integer = columns.GetUpperBound(0)
    Dim index As Integer = 0
    For Each column As String In columns
        ' Add a New Column to the Flat File Connection Manager
        Dim FlatFileColumn As IDTSConnectionManagerFlatFileColumn100 = connMgrFlatFileInnerObj.Columns.Add()
        FlatFileColumn.DataType = Wrapper.DataType.DT_STR
        FlatFileColumn.ColumnWidth = 255
        'FlatFileColumn.ColumnDelimiter = Dts.Variables("User::FieldDelimiter").Value.ToString
        If index < indexMax Then
            FlatFileColumn.ColumnDelimiter = Dts.Variables("User::v_FieldDelimiter").Value.ToString 'vbTab
        Else
            FlatFileColumn.ColumnDelimiter = vbCrLf
        End If
        FlatFileColumn.ColumnType = "Delimited"
        ' Use the Import File Field name to name the Column
        Dim columnName As IDTSName100 = TryCast(FlatFileColumn, IDTSName100)
        columnName.Name = column

        index = index + 1
    Next


    ' Add an OLEDBSource connection manager to the package.  
    '  Dim cmSource As ConnectionManager = package.Connections.Add("OLEDB")
    '  cmSource.Name = "OLEDB ConnectionManager Source"
    '  cmSource.ConnectionString = "Data Source=TKNSQL11.restest.bank,2900;" &
    '"Initial Catalog=Bazel_SSIS_Automation;Provider=SQLOLEDB.1;" &
    '"Integrated Security=SSPI;"

    ' Add an OLEDBDest connection manager to the package.  
    Dim cmDest As ConnectionManager = package.Connections.Add("OLEDB")
    cmDest.Name = "OLEDB ConnectionManager Destination"
    'cmDest.ConnectionString = "Data Source=TKNSQL11.restest.bank,2900;" &
    '      "Initial Catalog=Bazel_SSIS_Automation;Provider=SQLOLEDB.1;" &
    '      "Integrated Security=SSPI;" 'Dts.Variables("User::Dest_ADOConnection").Value
    cmDest.ConnectionString = Dts.Variables("User::Dest_ADOConnection").Value.ToString

    ' Add an OLE DB source to the data flow.  
    'Dim componentSource As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.New()
    'componentSource.Name = "OLEDBSource"
    'componentSource.ComponentClassID = "Microsoft.OLEDBSource"

    ' Add an TextFile source to the data flow.  
    Dim componentSource As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.New()
    componentSource.Name = "FlatFileSource"
    componentSource.ComponentClassID = "Microsoft.FlatFileSource"

    ' Get the design time instance of the component.  
    Dim instanceSource As CManagedComponentWrapper = componentSource.Instantiate()

    ' Initialize the component.  
    instanceSource.ProvideComponentProperties()

    ' Specify the connection manager.  
    If componentSource.RuntimeConnectionCollection.Count > 0 Then
        componentSource.RuntimeConnectionCollection(0).ConnectionManager =  DtsConvert.GetExtendedInterface(package.Connections(cmTextFile.Name))
        componentSource.RuntimeConnectionCollection(0).ConnectionManagerID = package.Connections(cmTextFile.Name).ID
    End If

    ' Set the custom properties OLEDB.  
    'instanceSource.SetComponentProperty("AccessMode", 2)
    'instanceSource.SetComponentProperty("SqlCommand", "Select * from dbo.AU_Tbl_Jobs")
    'Set the custom properties FLATFILE.  
    'instanceSource.SetComponentProperty("Format", "Delimited")
    'instanceSource.SetComponentProperty("HeaderRowDelimiter", "_x000D__x000A_") '"{CR}{LF}"
    'instanceSource.SetComponentProperty("TextQualified", "Delimited")

    ' Reinitialize the metadata.  
    instanceSource.AcquireConnections(vbNull)
    instanceSource.ReinitializeMetaData()
    instanceSource.ReleaseConnections()


    'Add Row Count
    Dim RowCountVar As Microsoft.SqlServer.Dts.Runtime.Variable = package.Variables.Add("RowCountVar",  False, "User", 0)
    'package.Variables.Add("RowCountVar", False, "User", 0);
    Dim componentRowNumber As IDTSComponentMetaData100 =  dataFlowTask.ComponentMetaDataCollection.New()
    componentRowNumber.Name = "RowNumberTransformation"
    componentRowNumber.ComponentClassID = "Microsoft.RowCount"
    Dim instanceRowNumber As CManagedComponentWrapper = componentRowNumber.Instantiate()
    instanceRowNumber.ProvideComponentProperties()
    ' Set the variable name property
    instanceRowNumber.SetComponentProperty("VariableName", "User::RowCountVar")
    ' Connect the two components together
    Dim path0 As IDTSPath100 = dataFlowTask.PathCollection.New()
    path0.AttachPathAndPropagateNotifications(componentSource.OutputCollection(0),  componentRowNumber.InputCollection(0))


    ' Add an OLE DB destination to the data flow.  
    Dim componentDest As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.New()
    componentDest.Name = "OLEDBDestination"
    componentDest.ComponentClassID = "Microsoft.OLEDBDestination"
    ' Get the design time instance of the component.  
    Dim instanceDest As CManagedComponentWrapper = componentDest.Instantiate()
    ' Initialize the component.  
    instanceDest.ProvideComponentProperties()

    ' Specify the connection manager.  
    If componentDest.RuntimeConnectionCollection.Count > 0 Then
        componentDest.RuntimeConnectionCollection(0).ConnectionManager =  DtsConvert.GetExtendedInterface(package.Connections(cmDest.Name))
        componentDest.RuntimeConnectionCollection(0).ConnectionManagerID =  package.Connections(cmDest.Name).ID
    End If

    ' Set the custom properties.  
    instanceDest.SetComponentProperty("AccessMode", 0)
    'instanceDest.SetComponentProperty("OpenRowset", "X_STG_POLICY_DIM")  'Dts.Variables("User::Dest_TableName").Value
    instanceDest.SetComponentProperty("OpenRowset", Dts.Variables("User::Dest_TableName").Value.ToString)
    ' Reinitialize the metadata.  
    instanceDest.AcquireConnections(vbNull)
    instanceDest.ReinitializeMetaData()
    instanceDest.ReleaseConnections()

    ' Create the path.  
    Dim path1 As IDTSPath100 = dataFlowTask.PathCollection.New()
    path1.AttachPathAndPropagateNotifications(componentRowNumber.OutputCollection(0), componentDest.InputCollection(0))

    ' Get the destination's default input and virtual input.  
    Dim input As IDTSInput100 = componentDest.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.  
        instanceDest.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
        'instanceDest.MapInputColumn(input.ID, vColumn.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);
    Next

    Dim i As Integer
    Dim inputCol As IDTSInputColumn100
    Dim externalCol As IDTSExternalMetadataColumn100
    For i = 0 To input.InputColumnCollection.Count - 1
        inputCol = input.InputColumnCollection(i)
        externalCol = input.ExternalMetadataColumnCollection(i)
        instanceDest.MapInputColumn(input.ID, inputCol.ID, externalCol.ID)
    Next

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

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

    Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New      Microsoft.SqlServer.Dts.Runtime.Application()

    app.SaveToXml("C:UsersAP0UQVBtest.dtsx", package, Nothing)

    'Dim pkgResults As DTSExecResult

    '        pkgResults =
    package.Execute()
    Dts.Variables("User::RowCount").Value = package.Variables("User::RowCountVar").Value
    Dts.Variables("User::v_ParentRowCount").Value = package.Variables("User::RowCountVar").Value

    Dim v_Error_Description As String = ""
    Dim pkgError As DtsError

    If package.Errors.Count > 0 Then
        For Each pkgError In package.Errors

            Console.WriteLine("ErrorCode       : {0}", pkgError.ErrorCode)

            Console.WriteLine("  SubComponent  : {0}", pkgError.SubComponent)

            Console.WriteLine("  Description   : {0}", pkgError.Description)

            v_Error_Description = v_Error_Description + " ### " + pkgError.Description

        Next
        Dts.Variables("User::v_Error_Description").Value = v_Error_Description
        Dts.Events.FireError(0, "Load destination", v_Error_Description, "", 0)
        Dts.TaskResult = ScriptResults.Failure
    Else
        Dts.TaskResult = ScriptResults.Success
    End If

End Sub


#Region "ScriptResults declaration"
'This enum provides a convenient shorthand within the scope of this class for setting the
'result of the script.

'This code was generated automatically.
Enum ScriptResults
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum

#End Region

End Class