Solved

SSIS Dynamic Column Mapping to Destination Columns

Posted on 2013-02-01
6
6,379 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tempdb Contention - SQL SERVER 10 41
View SQL database for NPS accounting 4 20
DMV Script to find how many times statistics are utilized 2 30
SQL Query 20 22
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

739 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