Post reply

Create New Excel Sheet if current sheet is full(65536) using SSIS

  • August 2, 2010 at 3:45 am

    #227364

    Hi All

    I have created an excel sheet using create table.[In SSIS Execute SQL task]

    I am inserting records into the excel sheet from one table.

    It is working file and i can see the values.

    But i am getting error when the records is exceeds 65535 i.e. record size per excel sheet.

    So i need to create another sheet that will contain rest records dynamically.

    [i.e. if record is more than 65536 than it will create another sheet and store all the records.

    Please help me to do it.

    thanks in Advance.

    Rohit

  • August 2, 2010 at 4:00 am

    #1200896

    You might consider using the export to Excel 2007

    Raunak J

  • August 2, 2010 at 5:56 am

    #1200938

    Even better...make table partitions of your dataset.

    then send each partition to a respective sheet

    Raunak J

  • August 2, 2010 at 6:57 am

    #1200971

    Raunak Jhawar (8/2/2010)

    Even better...make table partitions of your dataset.

    then send each partition to a respective sheet

    I still think your first answer was better (Excel 2007). Even better than that, however, is to opt for .CSV as your output format.

  • August 3, 2010 at 7:18 am

    #1201496

    Thanks For your suggestion.

    I am using SSIS 2005. and it is not possible to export records to Excel 2007.

    [Since it is supporting only .xls files.]

  • August 3, 2010 at 7:29 am

    #1201507

    Rohit I have another solution...considering the restrictions posed

    You may use variables TO and FROM variables to filter your query inside a foreach loop

    (Assuming you have implemented ROW_NUMBER() to get distinct row number's)

    For instance you have 100000 records

    Thus your loop will run 2 times (Loop_Count=No. of records\Excel number of rows)

    In side foreach

    you will have an EST(To create new excel sheet) followed by DFT ( To Perform export)

    and Script Component to modify your variables

    Thus for first run...TO=1 FROM=65536

    second run TO=65537 FROM=100000

    Raunak J

  • August 3, 2010 at 7:39 am

    #1201516

    Link below will help you create multiple sheets.. I have used the same method Raunak Suggested in the past and i think its a good work around if cannot use XL 2007 or Csv

    http://www.rafael-salas.com/2008/03/ssis-and-dynamic-excel-destinations_01.html]

  • August 3, 2010 at 7:50 am

    #1201532

    rohit.kumar.barik

    May I suggest that you read this article, which contains a wealth of sample code ... It has code to name Excel spread sheets, add work sheets, write to a selected sheet, etc., etc.

    http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

    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 3, 2010 at 8:03 am

    #1201548

    Surely, when you start getting to the realms of datasets with more than 65k rows, a spreadsheet is not the right repository. You need a database solution (Access maybe) or a flat-file solution.

    Unless you're telling me that the recipient of a multi-sheet Excel file containing several-hundred-thousand rows is actually going to build multi-sheet analytics on it, or is an accountant (!), this requirement should be reviewed asap, in my opinion.

    Go ahead - prove me wrong 🙂

  • August 3, 2010 at 10:23 pm

    #1201876

    Yes Phil you are correct

    Raunak J

  • August 27, 2010 at 10:09 am

    #1213076

    Raunak--

    Could you please expand on your solution (provide details) using the TO/FROM variables? I would like to implement, but am somewhat new to SSIS and need a bit of hand holding.

    Thank you!!

Viewing 11 posts - 1 through 10 (of 10 total)

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