SQL Server Performance

data mappings

Discussion in 'Getting Started' started by thomasttt, Oct 8, 2007.

  1. thomasttt New Member

    I'm not sure if I can do this or not - but - I have an excel sheet that needs to be parsed into sql server. However, Although the transfer should be simple, I cannot use the simple dts wizard because all the header in the excel file are in different cells throughout the sheet. I alway though that as long as the source and destination were mapped it did not really matter where the excel headers were located and everything under that header would be parse. For example, I have the excel and sql server connection in designer. then i dragged over the Active X script tasks and simply added:
    ' Visual Basic Transformation Script
    ' Copy each source column to the
    ' destination column
    Function Main()
    DTSDestination("PropertyId") = DTSSource("PropertyId")
    DTSDestination("AppraiserId") = DTSSource("AppraiserId")
    DTSDestination("Appraisal Date") = DTSSource("ApprailalDate")
    Main = DTSTransformStat_OK
    End Function
    Where the source could start on A12 for examlple (destination col would be the col in sql server obviously) Has anyone done mapping of this nature. Is it even possible? Also, any way the A,B,C .... header on top of the excel sheet be turned off when viewing transformtations
    Any help --- VERY Appriciated - Thanks
  2. martins New Member

    Getting excel data into SQL Server can be a bit tricky sometimes, and the Excel connection isn't the most flexible.
    You will be able to do what you want, as long as the headings are always in the top row of your sheet. I would suggest you rather try a "Transform Data" task which will enable you to map from any source column to any destination column.
    Depending on the data in the excel sheet, you might not be able to get rid of the "A, B, C..." mentioned above. This would typically happen if your first column (column A) in Excel does not contain any data.
    If your source only starts at A12 as mentioned above, DTS will see the heading (which I assume is in A12) as part of the data and all the rows above that as empty rows...and it would be impossible to import unless you import as is and try and manupilate it with some sql query.
    Hope this helps.

Share This Page