Limitation of SSIS Excel Source/Destination
SSIS comes with out of the box support for read/write to Excel. But its very restrictive if you want to make things dynamic because any metadata inside DataFlow cannot be changed at runtime. Here are few problems using native Excel Source or Destination.
- Metadata cannot be changed at runtime
- Datatype guessing nightmare – only first few rows scanned and wrong datatype selected
- Cannot export multiple tables to excel
- Cannot split data into multiple tabs based on certain grouping criteria
- Cannot specify location where data has to be written
SSIS export to excel dynamically using Excel Export Task
If you want to generate excel dynamically without any of above hassle then you can check SSIS Excel Export Task. It comes with many advantages not found in native Microsoft SSIS Excel Destination
Here is the list of few features for SSIS Excel Export Task
- Export multiple tables/views to Excel files (e.g. Sales% or do SalesJan|SalesFeb)
- Support for all major office versions (e.g. Office 2003, 2007, 2013+)
- Export SQL query output to Excel file
- Completely dynamic approach without limitation of DataFlow Stict Metadata
- Option to export data to specific starting location in Excel Sheet (e.g A5 will start writng to 5th row 1st column)
- Support for Password protected excel file
- Option to clear range of cell before writing data to existing excel workbook (e.g. A1:D5000 will clear A to D columns from 1st to 5000 rows)
- Automatically Split exported Excel data into multiple files by Size or Number of records
- Automatically Split exported Excel data into multiple files or tabs when Split By Column (e.g. SplitBy=Country will create new file for each country, if SplitOnTab option specified then data written to new excel tab rather than file for each new group)
- Support for Compression and Append
- Fully managed means works out of the box on 32-Bit and 64-Bit without any change (Native SSIS Excel functionality is 32bit only).
- Support for SQL Server 2005, 2008, 2012, 2014 (32 bit and 64 bit)
SSIS Export to Excel File – Generate Excel files for selected tables/views
SSIS Export to Excel File – Select Target Path and other options
SSIS Export to Excel File – Split Excel Data into Multiple files
SSIS Export to Excel File – Split Excel Data into Multiple sheets
SSIS Export to Excel File – Date Formatting, Encoding Options
SSIS Export to Excel File – Execution Log
Conclusion
In this post we have seen how excel generation can be headache when you have dynamic metadata. You can use SSIS Excel Export Task to solve many of these challenges.
