locked
possible to import a spreetsheet with dynamic column name? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    In the spreadsheet, the first line is the header which tell the column names to be used, the rest are the data.

     

    So if we have the heading in the excel as

    col1  col2  col3

     

    then we load the data into a table with 3 columns whose names are col1,col2,col3

     

    And the heading is

    tt1 tt2 tt3 tt4, then load it into a table with 4 columns whose names are tt1,tt2,tt3,tt4

     

    The incoming file is changing, I will to load it into a staging table with exactly the same structure as the spreadsheet.

    Friday, April 29, 2011 8:09 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    when you  have dynamic metadata, you can not use data flow task. because data flow task doesn't support dynamic metadata.

    but you can use another solution:

    use a foreach loop container to loops through your excel files.

    then add an Execute SQL Task inside the foreach loop,

    and set a variable value dynamically with the script for import.

    you can use OpenRowSet method to select data from your excel file and insert into destination sql server table.

    this is a sample command for OpenRowSet:

     

    SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

    also this is good reference :

    http://support.microsoft.com/kb/321686

    http://www.rad.pasfu.com
    Friday, April 29, 2011 9:29 AM