Export data from sql server to excel without excel sheet constraint

Question

Hi

I need to export data from sql server table to excel sheet using SSIS. But my package stops because the excel sheet gets full. And I need to use another excel sheet for rest of the data. 

How can I do that? or Is there work around to achieve that goal.

Answer 1

yep. thats possible. you just need a loop to break every 65536 or 1,048,576  based on the version. then inside loop create a new excel sheet using execute sql task and then pass it as value for the destination

see below link for similar example where I create sheets on the fly and populate it with data

http://visakhm.blogspot.in/2013/09/exporting-sqlserver-data-to-multiple.html

The only difference in your case would be looking at rowcount and creating a new sheet for every max row value.

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 2

I think this idea is in general not too elegant to use Excel to fill with data.

How would you assemble it all back?

If you need to spread the data the fastest approach could be the Balance Data Load Distributor

---

Arthur My Blog

Answer 3

You can also consider to import data into Excel PowerPivot. That way there will be no limits on number of rows.

http://www.microsoft.com/en-us/bi/powerpivot.aspx

Answer 4

Hi Rocket,

How many rows will be exported? Do you want to export to Excel 2003 or Excel 2007 format? In Excel 2007, it uses Office Open XML format (.xlsx) and the maximum rows per worksheet is limited to 1,048,576. Even in SSIS 2005, we can also export to Excel 2007 format as long as we install the Microsoft Office 12.0 Access Database Engine OLE DB driver which can be downloaded from 2007 Office System Driver: Data Connectivity Components. So, if the SQL table has no more than 1 million rows, you can select to export o Excel 2007.

In your scenario, if you need to Excel 2003 format, you can use For Loop Container and Script Task to loop the SQL table every 50000 rows, and export to different Excel worksheets dynamically:
http://msbipracticals.blogspot.com/2011/10/create-new-excel-sheet-when-source.html.

Regards,

---

Mike Yin
TechNet Community Support