Very common scenario when there is a need to export a table having more than 65536 rows to EXCEL. Best solution to this is use Excel 2007 (.xlsx). But the problem SSIS Excel Source or Destination does not provide support for it.
We can still connect to Excel 2007 using SSIS and export/import data.
HOW..lets see in an example
I have have table having 70,000 rows and we will pump this to an Excel file
1. The table
2. The Excel work:
Create an OleDb connection to Excel in Connection Manager
a. Use Microsoft Office 12.0 Access Database Provider from Provider list
b. Give Path to the xlsx file in Server or File name
c. Go to the ALL tab and give Excel 12.0 as Extended Property
3. Now use OleDB Destination (careful we are using Excel Destination here) and give Excel Connection Mgr, select the Sheet name, Map the columns and there it is.
4. Run
