locked
Reading data from Excel source using Excel Source in SSIS RRS feed

  • Question

  • Hi

    I am using SSISto laod data to my DB.

    My excel source has some definisitons in row 1,column headings in row 2 and data from row 3.

    I have to ignore row 1 and consider column headings and data while reading the data.

    So i made a excel source given connections to excel sheet and wrote query to read data as select * from [sheet1$A3:k].

    THis reads data from third row as required. but returns only 256 rows but i have 1000s of rows in esxcel sheet :( .

     

    Any idea why it returns only 256 rows?


    Ravi.S Chennai, Tamil Nadu, India
    Tuesday, December 20, 2011 6:32 AM

All replies

  • select * from [sheet1$A3:k].

    I think the issue is range you have specified in the select query. May be you can try to put select * from [sheet1$A3:k1000]. You can try the same using OperRowSet property

    SSIS: Read and Export Excel data from nth Row


    Vikash Kumar Singh || www.singhvikash.in
    Tuesday, December 20, 2011 8:20 AM
  • Hi Vikash

     

    Thanks for your reply.

    I noticed there not a problem with the range.

    Once i open the excel sheet do a save again, it reads all 30,000 rows.

    But if i use excel sheet directly without open and save it reads only 254 rows. THis is the problem now for me


    Ravi.S Chennai, Tamil Nadu, India
    Tuesday, December 20, 2011 8:30 AM
  • What are the results if you take his advice and set the range to A3:K1000?
    Todd McDermid's Blog Talk to me now on
    Tuesday, December 20, 2011 4:18 PM
  • Even then i got only 256 rows.

    The problem was with the excel sheet End of Line(row).

    We resolved it and now used [sheet1$A3:k] to read all data and it works fine.


    Ravi.S Chennai, Tamil Nadu, India

    Tuesday, February 7, 2012 4:13 PM
  • Hi Ravi, How did you solve the EOL issue in excel. i am facing a similar problem. SSIS package just inserts the first 256 rows. Please help.

    Thanks.

    Wednesday, August 20, 2014 7:55 PM
  • Ravi, I am also facing the same issue. Could you please suggest how to fix this issue and how to identify end of row issue in excel spread sheets. I am wondering bcos of this strange behavior which I never faced. 
    Wednesday, June 6, 2018 6:50 PM