Limitations of the SSIS Excel Source/Destination
SSIS comes with out-of-the-box support for reading and writing to Excel. However, it’s very restrictive if you want to make things dynamic, as any metadata inside DataFlow cannot be changed at runtime. Here are a few problems with using the native Excel Source or Destination.
- Metadata cannot be changed at runtime.
- Datatype guessing nightmare – only the first few rows are scanned, and the wrong datatype is selected.
- Cannot export multiple tables to Excel.
- Cannot split data into multiple tabs based on specific grouping criteria
- Cannot specify the 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 the above hassle, then you can check the SSIS Excel Export Task. It comes with many advantages not found in the native Microsoft SSIS Excel Destination
Here is the list of a few features for the 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 an Excel file.
- A completely dynamic approach without limitations on DataFlow Strict Metadata.
- Option to export data to a specific starting location in an Excel Sheet (e.g, A5 will start writing to the 5th row, 1st column).
- Support for a password-protected Excel file.
- Option to clear a range of cells before writing data to an 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 a new file for each country). If the SplitOnTab option is specified, then data is written to a new Excel tab rather than a file for each new group.
- Support for Compression and Append.
- Fully managed means it works out of the box on both 32-bit and 64-bit systems without any changes (Note that Native SSIS Excel functionality is 32-bit only).
- Support for SQL Server 2005, 2008, 2012, 2014 (32-bit and 64-bit).
Step-By-Step
SSIS Export to Excel File – Generate Excel files for selected tables/views
SSIS Export to Excel File – Generate Excel files for selected tables/views
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 a headache when you have dynamic metadata. You can use the SSIS Excel Export Task to solve many of these challenges.
