SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
rohit.kumar.barik
rohit.kumar.barik
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 49
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
Raunak Jhawar
Raunak Jhawar
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1651 Visits: 1944
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
Raunak Jhawar
Raunak Jhawar
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1651 Visits: 1944
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
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18734 Visits: 20452
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
rohit.kumar.barik
rohit.kumar.barik
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 49
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.]
Raunak Jhawar
Raunak Jhawar
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1651 Visits: 1944
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
divyanth
divyanth
SSC-Addicted
SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)

Group: General Forum Members
Points: 441 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]
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7889 Visits: 25280
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
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18734 Visits: 20452
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Raunak Jhawar
Raunak Jhawar
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1651 Visits: 1944
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search