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

  • Question

  • 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


  • 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 :


    Friday, April 29, 2011 9:29 AM