Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Create New Excel Sheet if current sheet is full(65536) using SSIS Expand / Collapse
Author
Message
Posted Monday, August 02, 2010 3:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 03, 2014 8:56 AM
Points: 14, Visits: 34
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
Post #962051
Posted Monday, August 02, 2010 4:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:58 AM
Points: 1,042, Visits: 1,828
You might consider using the export to Excel 2007

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #962057
Posted Monday, August 02, 2010 5:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:58 AM
Points: 1,042, Visits: 1,828
Even better...make table partitions of your dataset.
then send each partition to a respective sheet



Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #962106
Posted Monday, August 02, 2010 6:57 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 4,828, Visits: 11,178
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #962147
Posted Tuesday, August 03, 2010 7:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 03, 2014 8:56 AM
Points: 14, Visits: 34
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.]
Post #962774
Posted Tuesday, August 03, 2010 7:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:58 AM
Points: 1,042, Visits: 1,828
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



Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #962790
Posted Tuesday, August 03, 2010 7:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 17, 2013 4:51 PM
Points: 215, Visits: 556
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]
Post #962799
Posted Tuesday, August 03, 2010 7:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 5,469, Visits: 23,458
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

Before posting a performance problem please read
Post #962815
Posted Tuesday, August 03, 2010 8:03 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 4,828, Visits: 11,178
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



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #962832
Posted Tuesday, August 03, 2010 10:23 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:58 AM
Points: 1,042, Visits: 1,828
Yes Phil you are correct

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #963222
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse