locked
Import Data via SSIS, from an excel named range to master excel named range RRS feed

  • Question

  • HI I am trying to import data from  multiple excel files (in SSIS) into a master file. Each excel file contains data in 4 named ranges and the master is suppose to contain the aggregate of each named range during the import process. Any suggestions as to how this can be achieved will be appreciated.

    RRS


    • Edited by srituraj Monday, May 7, 2012 3:38 AM
    Monday, May 7, 2012 3:38 AM

Answers

  • Also instead of .Net you can simply type

    SELECT * FROM <Named Range>

    and you will get the specific data you want from Range defined in that excel

    • Proposed as answer by EitanBlumin Monday, May 7, 2012 5:49 AM
    • Marked as answer by Eileen Zhao Friday, May 11, 2012 3:30 AM
    Monday, May 7, 2012 4:18 AM

All replies

  • I guess for named ranged you might need to use a .Net script - but other wise you can use the below SQL command in to fetch a set of rows/Columns from Excel

    SELECT * FROM [Sheet1$A2:B10]

    Hope this helps


    Monday, May 7, 2012 4:14 AM
  • Also instead of .Net you can simply type

    SELECT * FROM <Named Range>

    and you will get the specific data you want from Range defined in that excel

    • Proposed as answer by EitanBlumin Monday, May 7, 2012 5:49 AM
    • Marked as answer by Eileen Zhao Friday, May 11, 2012 3:30 AM
    Monday, May 7, 2012 4:18 AM
  • On your Data Flow palette, place an Excel source.

    The Excel source then gets associated with a connection to the file, AS defined under Connection Managers.

    Set Data Access mode to "Table or view"

    Select a specific sheet under "Name of the excel sheet"

    On the properties tab of the Excel Data source, define the named range under the property "Open Row Set", in this format Sheet1$A10:B15.

    • Proposed as answer by code(monkey) Thursday, September 20, 2012 8:11 AM
    Thursday, September 20, 2012 8:11 AM
  • On your Data Flow palette, place an Excel source.

    The Excel source then gets associated with a connection to the file, AS defined under Connection Managers.

    Set Data Access mode to "Table or view"

    Select a specific sheet under "Name of the excel sheet"

    On the properties tab of the Excel Data source, define the named range under the property "Open Row Set", in this format Sheet1$A10:B15.

    A nice theory but does not appear to work on SSIS2012.

    And while the proposed answer worked, I'm getting a "No named columns were returned" (even though Preview works)... which prevents continuation

    EDIT:

    to follow up on that, changing it in the Properties does look to work, provided you have no spaces in the tab name you're attempting to adjust. When there's a space in the tab name, it puts single quotes around the name, I attempted to put the range inside the quotes, outside the quotes, change the quotes to square brackets, double quotes, backticks with no luck. Another "well" documented oddity from Microsoft...

    • Edited by RyanAB Wednesday, November 12, 2014 3:09 AM
    Wednesday, November 12, 2014 2:59 AM