SSIS Dynamic Column Mapping to Destination Columns

Hi Experts

Scenario: I have a flat file with different column names to the destination table in SQL.

How should I approach this problem?

Currently I'm trying to use a script task. I'm creating a package manually in the script task and I'm creating the connections to the 2 datasources manually in the package as well. So far I have managed to get hold of the collection of columns from the flat file. I wanted to get hold of the collection of columns from destination table in SQL and then map the columns together manually. I cannot get access to the destination columns so that I can loop through both sets of columns and map them appropriately.

Is this the right way to do this or is there and easier way?

Who is Participating?

Improve company productivity with a Business Account.Sign Up

dfincham28Connect With a Mentor Author Commented:
Fortunately I have managed to solve my own problem. For the rest of you who get stuck with something similar I will summarise what I did. I create two dataflows. The first data flow queried a table that held the column mapping. The result of the table query I stored in Record Set Destination. The record set I put into a user variable of type object so that it would be available in my script that was doing the mapping.

Below is the code I used in a script component
#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Linq

#End Region

' This is the class to which to add your code.  Do not change the name, attributes, or parent
' of this class.
<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
  Inherits UserComponent

  Dim dt As DataTable = Nothing

  Dim conMgr As IDTSConnectionManager100

  Dim sqlConn As SqlConnection

  Dim sqlCmd As SqlCommand = New SqlCommand

  Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

    conMgr = Me.Connections.Connection

    sqlConn = CType(conMgr.AcquireConnection(Nothing), SqlConnection)

  End Sub

  Public Overrides Sub PreExecute()

    With sqlCmd
      .Connection = sqlConn
      .CommandType = CommandType.StoredProcedure
      .CommandText = "InsProcs.insCashTransactions"

      .Parameters.Add("@ImportFileId", SqlDbType.Int)
      .Parameters.Add("@Import_AccountId", SqlDbType.Int)
      .Parameters.Add("@Currency_Account", SqlDbType.NVarChar)
      .Parameters.Add("@TransactionId_Related", SqlDbType.Int)
      .Parameters.Add("@OrderId", SqlDbType.Int)
      .Parameters.Add("@Date_Transaction", SqlDbType.Date)
      .Parameters.Add("@Date_Value", SqlDbType.Date)
      .Parameters.Add("@TotalCT_Gross", SqlDbType.Money)
      .Parameters.Add("@Currency_Transaction", SqlDbType.NVarChar)
      .Parameters.Add("@FeeCT_Transfer", SqlDbType.Money)
      .Parameters.Add("@ExchangeRate_CTtoCA", SqlDbType.Money)
      .Parameters.Add("@TotalCA_Gross", SqlDbType.Money)
      .Parameters.Add("@FeeCA_Transfer", SqlDbType.Money)
      .Parameters.Add("@TotalCA_Net", SqlDbType.Money)
      .Parameters.Add("@PartnerAccountKey", SqlDbType.NVarChar)
      .Parameters.Add("@Comment", SqlDbType.NVarChar)

    End With

    If dt Is Nothing Then

        dt = New DataTable

        Dim da As OleDbDataAdapter = New OleDbDataAdapter

        da.Fill(dt, Me.Variables.ColumnLookup)

      Catch ex As Exception

      End Try

    End If

  End Sub

  ' This method is called after all the rows have passed through this component.
  ' You can delete this method if you don't need to do anything here.
  Public Overrides Sub PostExecute()
    ' Add your code here

  End Sub

  'This method is called once for every row that passes through the component from Input0.
  'Example of reading a value from a column in the the row:
  ' zipCode = Row.ZipCode
  'Example of writing a value to a column in the row:
  ' Row.ZipCode = zipCode
  Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    ' Add your code here
    With sqlCmd
      .Parameters("@ImportFileId").Value = dt.Rows(0).Item("ImportFileSpecId")
      .Parameters("@Import_AccountId").Value = 123 'Row.Account
      .Parameters("@Currency_Account").Value = Row.AccountCurrency
      .Parameters("@TransactionId_Related").Value = Row.TransactionNumber
      .Parameters("@OrderId").Value = Row.OrderNumber
      .Parameters("@Date_Transaction").Value = EstablishDate(Row.Date)
      .Parameters("@Date_Value").Value = EstablishDate(Row.ValueDate)
      .Parameters("@TotalCT_Gross").Value = 0
      .Parameters("@Currency_Transaction").Value = Row.CashCurrency
      .Parameters("@FeeCT_Transfer").Value = Row.TransferFee
      .Parameters("@ExchangeRate_CTtoCA").Value = Row.ConversionRate
      .Parameters("@TotalCA_Gross").Value = 0
      .Parameters("@FeeCA_Transfer").Value = Row.BookedAmountTransferFee
      .Parameters("@TotalCA_Net").Value = 0

      '************ THIS IS DOING THE LOOKUP AND THE MAPPING ****************************************************************
      .Parameters("@PartnerAccountKey").Value = CallByName(Row, EstablishColumnName("PartnerAccountKey"), CallType.Get, Nothing)
      .Parameters("@Comment").Value = Row.Comment

    End With

  End Sub

  Private Function EstablishDate(ByVal DateString As String) As Date

    Return New Date(CType(DateString.Substring(0, 4), Integer), CType(DateString.Substring(4, 2), Integer), CType(DateString.Substring(6, 2), Integer))

  End Function

  Private Function EstablishColumnName(ByVal DatabaseTableFieldName As String) As String

    Return CType((From myRow In dt.AsEnumerable()
                 Where myRow.Item("ImportFileSpecId").Equals(3) AndAlso myRow.Item("DatabaseTableFieldname").Equals(DatabaseTableFieldName)
                 Select myRow.Item("FileFieldName")).FirstOrDefault, String)

  End Function

  Public Overrides Sub ReleaseConnections()


  End Sub

End Class
Simone BSenior E-Commerce AnalystCommented:
The easiest way is to use the OLE DB Destination Editor. The column names don't have to match.

dfincham28Author Commented:

Sorry I should have been more clear with my question. We are not using the designer because the columns will be determined at runtime. The column mappings will be held in a SQL table which we will use in the script to do the mapping.

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Simone BSenior E-Commerce AnalystCommented:
Sounds brilliant!

Sorry I wasn't able to help.
dfincham28Author Commented:
My answer was the most accurate
Hello @dfincham28, can you post the package?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.