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

How to load one million records into excel using ssis Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 12:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 13, 2014 6:01 AM
Points: 17, Visits: 148
Hi,
I have one million records in sql server global temp table.i want to dump into excel sheet.

please suggest me of best way.
Thanks in advance...


Cheer's
Rajesh
Post #1398218
Posted Wednesday, December 19, 2012 2:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 2,931, Visits: 2,948
Hi,
You must use office 2007 or higher version for a million records. Max rows an excel sheet can accept is 1,048,576.

SSIS is very flexible for such tasks like yours. I think this blog will help you
http://cavemansblog.wordpress.com/2009/04/17/ssis-export-data-from-sql-server-2005-to-excel/

Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1398255
Posted Wednesday, December 19, 2012 3:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 13, 2014 6:01 AM
Points: 17, Visits: 148
Thank's IgorMi.

if data will be more than 1.5 millions,then what is solution for this?because i have 1.6 millions of records.

Please suggest me....


Cheer's
Rajesh
Post #1398275
Posted Wednesday, December 19, 2012 3:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 5,218, Visits: 5,067
Do you have to export to excel? Can you not export to another format, CSV, txt etc?

If it has to be xlsx then you will need to export 1 million rows into 1 sheet, then export the next 1 million rows to another sheet etc etc until all rows are exported.

What is the reasoning behind what you are trying to do? What is the expected outcome? Maybe there is a better way to do what you are trying to do.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1398278
Posted Wednesday, December 19, 2012 5:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 2,931, Visits: 2,948
rajeshpalo2003 78748 (12/19/2012)
Thank's IgorMi.

if data will be more than 1.5 millions,then what is solution for this?because i have 1.6 millions of records.

Please suggest me....


Hi,

You can use for example two OLE DB sources with Data access mode: sql command. You can make two selects: one selecting the first million rows and the other selecting the rest 0.6 million rows.
Export both sets into two different excel documents or sheets.

There are more ways to do your task.

Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1398320
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse