Solved

SSIS Dynamic Column Mapping to Destination Columns

Posted on 2013-02-01
6
6,222 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 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