none
multiple excel sheets

    Question

  • Hi,

    I had excel file it contains 10(It is not static) excel sheets. I want transfer/load the excel sheets data into different tables.

    sheet1 ----> Table1
    Sheet2 ----> Table2
    .
    .
    .
    .
    .
    Sheet10 ----> Table10

    What is the best approach to do above task in SSIS 2005.
    can u please provide examples on above task and it needs to be helpfull for me.

    Friday, April 22, 2011 5:12 PM

Answers

  • Structure is different (Columns and data types each and every sheet) and I need in SSIS 2005 and not using t-sql.

    you can use Execute SQL Task in SSIS 2005 to do what I proposed with OpenRowSet,

    first you should read all excel sheet names in an script task and fill it in an object type variable,

    then you can use a foreach loop to loops through these sheet names and fetch each sheet name in each iteration.

    then use an execute sql task inside foreach loop container and set your sql command dynamically to do dynamic OpenRowSet select into query based on source excel sheet and destination table name.

     

    let me know where you need more information?


    http://www.rad.pasfu.com
    Friday, April 22, 2011 7:14 PM

All replies

  • are the structure of all sheets and tables same? ( I mean number of columns , column names, data types )

    if yes, you can use a foreach loop with single data flow task using expression to do this dynamic data transfer

    if no, you can not use data flow task for this purpose,  but you can try other option: using OpenRowSet like this query:

     

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

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

    http://www.rad.pasfu.com
    Friday, April 22, 2011 6:41 PM
  • Kumar,

    You can retrieve data from multiple sheets as,


    1) Use FOREACH loop container with single data flow task.

    2) Use excel source in DFT

    3) Goto FOREACH loop container - > Rigt Cclick ->Edit - > Collection - >Change enumerator to FOREACH ADO Enumerator.

    4) Specify file path with VAriable exression.

    http://technet.microsoft.com/en-us/library/ms345182.aspx

    Hope that this will work for you.

    Thanks!

    CPatil


    MCTS 2008 & 2005 , MCITP 2008 -- Please remember to mark the post as answered if it answers your question.
    Friday, April 22, 2011 7:08 PM
  • Structure is different (Columns and data types each and every sheet) and I need in SSIS 2005 and not using t-sql.

    Friday, April 22, 2011 7:09 PM
  • Structure is different (Columns and data types each and every sheet) and I need in SSIS 2005 and not using t-sql.

    you can use Execute SQL Task in SSIS 2005 to do what I proposed with OpenRowSet,

    first you should read all excel sheet names in an script task and fill it in an object type variable,

    then you can use a foreach loop to loops through these sheet names and fetch each sheet name in each iteration.

    then use an execute sql task inside foreach loop container and set your sql command dynamically to do dynamic OpenRowSet select into query based on source excel sheet and destination table name.

     

    let me know where you need more information?


    http://www.rad.pasfu.com
    Friday, April 22, 2011 7:14 PM