Overview
Excel Connection Manager is SSIS Connection Manager for processing data in Microsoft Excel workbook. Use the API to build components based on Excel Connection Manager.
Parameters
Excel
Use the Excel page of the Excel Connection Manager dialog to specify properties for opening Excel workbook.
- Test Connection
- Confirm connection manager configuration by clicking Test Connection.
Specify Excel workbook file to open.
Specify if all clients should use the same connection. This parameter can be accessed from the Properties editor and has the options listed in the following table.
Value Description True All clients use same connection (default). False Every client use different connection.
Advanced
Use the Advanced page of the Excel Connection Manager dialog to specify additional properties if the default configuration is not working.
Specify Excel workbook format. This property has the options listed in the following table.
Value Description Automatic Automatically determines Excel workbook format from specified file extension (default). Excel 97-2003 97-2003 Excel workbook. Excel 2007 2007-2010 Excel workbook. Requires installation of .NET 3.0 or later. Pocket Excel Pocket Excel. Specify password to open Excel workbook.
Specify password to modify Excel workbook.
Specify to recalculate and update external references in Excel workbook on open. This property has the options listed in the following table.
Value Description True Excel workbook is recalculated on open. False Excel workbook is not recalculated on open (default). Optional. Specify custom string format for writing date/time values. The format used by the standard Excel connection is :
yyyy-MM-dd HH:mm:ss.fffffff
Knowledge Base
- Where can I find the documentation for the Excel Connection?
- How can I set the Date Format for dates in the Excel Destination?
- How to dynamically set the ExcelFilePath when looping through different excel files in a data flow.
- Error System.Exception 'SheetName' object not found.
- Why does my Excel Destination not overwrite the data correctly?
What's New
- New: Support for Excel 2019.
- New: A new parameter DateTimeFormat to specify string format when writing DateTime values.
- Fixed: Failed to open Excel files stored in SharePoint (Thank you, Kevin).
- Fixed: Failed to open certain Excel files (Thank you, Ada).
- Fixed: Failed to open password-protected Excel 2007 files.
- New: Introduced connection.
Related documentation
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.