Post reply

import into an excel spreadsheet issue

  • August 26, 2008 at 12:34 pm

    #123203

    hi guys i hope you can help me. I am working on a ssis package in which i need to create an excel spreadsheet using a query. My problem is that the query i need to use to create the spreadsheet results in above 100k rows. How can i make it so the file task put is in different worksheets within the spreadsheet?

  • August 26, 2008 at 1:13 pm

    #863347

    If you are using Excel 2007 be advised that some important old limitations have been removed:

    The total number of available columns in Excel

    Old Limit: 256 (2^8)

    New Limit: 16k (2^14)

    The total number of available rows in Excel

    Old Limit: 64k (2^16)

    New Limit: 1M (2^20)

    Above from http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx

    It would appear that your 100K rows can be accomodated with ease.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • August 26, 2008 at 1:20 pm

    #863355

    Unfortunately we are still using excel 2003

  • August 26, 2008 at 3:42 pm

    #863435

    This is always a problem with Excel and there is no tidy way to do this.

    If you don't have a way to descriminate between the records, you could add a row counter (script transform) and a conditional split in your transform. If Counter <= 60 sent rows to sheet 1. If counter > 60 and < 120 send rows to sheet 2... and so on.

    Not pretty, but it would work.

    HTH

    PS: Microsoft article to create a row counter is here

    Kindest Regards,

    Frank Bazan

  • September 4, 2008 at 2:37 pm

    #867441

    Thank you Frank, it worked perfectly. 😀

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply