Overview
The Excel Source Component is an SSIS Data Flow Component for consuming data from Excel worksheets.
Demonstration
Case Study
Parameters
General
Use the General page of the Excel Source dialog to specify the source Excel worksheet and region.
- Reset
- Click the Reset button to re-initialize the component metadata.
Select the COZYROC Excel connection manager. Note: This will not work if you select the standard Excel connection manager.
Related Topics: Excel Connection Manager
Select Excel worksheet as the source. If the specified sheet starts with the colon (:) symbol, the component will treat the remaining definition as a sheet index 2.0.
Indicate that the first row contains column names.
Specify a named range within the source Excel file.
Specify which columns from the source are to be used as the input region.
- Start - start column. The column numbering is 1-based.
- Width - number of columns.
Specify which rows from the source are to be used as the input region.
- Start - start row. The row numbering is 1-based.
- Height - number of rows. If set to -1, the component will read all used rows from the worksheet.
Specify the output type. This parameter has the options listed in the following table.
Value Description Formatted Value Output formatted cell value. Raw Value Output raw cell value. Formula Output cell formula. Specify whether to remove whitespace from start/end of column names.
Columns
Use the Columns tab to specify which columns are to be processed from the source Excel file and to specify new names for the columns being processed, if desired.
This table lists the columns, by name, that are available to be processed from the source Excel file. If there are columns that you do not want to process, uncheck them from the list.
You cannot use this table to add or delete columns in the source Excel file. To manipulate data in an external Excel file, use the COZYROC Excel Task.Shows the columns that will be processed from the source Excel file based on the columns selected in the Available Input Columns list.
This shows the column name that will be used during processing. The name of any column may be changed by typing over the text in this list. Each name must be unique.
Error Output
Use the Error Output page to select error handling options for the columns.
View the name of the data source.
View the selected source column.
Specify what should happen when an error occurs: ignore the failure, redirect the row, or fail the component.
Related Topics: Handling Errors in Data
Specify what should happen when a truncation occurs: ignore the failure, redirect the row, or fail the component.
View a description of the error.
Knowledge Base
- Where can I find the documentation for the Excel Source?
- How to dynamically set the ExcelFilePath when looping through different excel files in a data flow.
- Error System.Exception 'SheetName' object not found.
- How to configure the Excel Source Plus component to use particular worksheet when the sheet name is unknown?
- Excel Source Plus: How to modify the source region dynamically at runtime
What's New
- New: TrimColumnNames parameter to specify whether to remove whitespace from start/end of column names.
- Fixed: Processing of columns with type different from string failed for cultures that are not invariant (Thank you, John).
- New: Ability to reference sheet by index (:index) when the sheet name is unknown.
- New: Cell value format can now be configured with LocaleID property.
- New: Component can now read from named ranges pointing to tables.
- Fixed: Component failed with error when input cell value was larger than the output column length and the column truncation metadata was set to ignore (Thank you, Dave). For further information check this post.
- Fixed: Component failed with error "The type initializer for '#c.#rnf' threw an exception." when reading certain files (Thank you, Scott).
- Fixed: Component failed to process sheet with name containing white space at start or end (Thank you, John).
- Fixed: Component is now multithread-safe.
- New: Component now supports named ranges.
- Fixed: Component did not activate selected worksheet at runtime.
- New: Introduced component.
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.