Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSIS Dynamic Column Mapping to Destination Columns

Posted on 2013-02-01
6
Medium Priority
?
7,136 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
1
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

722 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