Solved

SSIS Dynamic Column Mapping to Destination Columns

Posted on 2013-02-01
6
6,703 Views
1 Endorsement
Last Modified: 2016-02-10
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?

Thanks
1
Comment
Question by:dfincham28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:Simone B
ID: 38844043
The easiest way is to use the OLE DB Destination Editor. The column names don't have to match.

Image
0
 

Author Comment

by:dfincham28
ID: 38850072
Hi

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.

Thanks
0
 

Accepted Solution

by:
dfincham28 earned 0 total points
ID: 38854367
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
      Try

        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()
    MyBase.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
      .ExecuteNonQuery()

    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()

    conMgr.ReleaseConnection(sqlConn)

  End Sub


End Class
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 11

Expert Comment

by:Simone B
ID: 38855149
Sounds brilliant!

Sorry I wasn't able to help.
0
 

Author Closing Comment

by:dfincham28
ID: 38922751
My answer was the most accurate
0
 

Expert Comment

by:ernesth65
ID: 39090991
Hello @dfincham28, can you post the package?

Thanks
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question