Overview
The Excel Destination Component is an SSIS Data Flow Component for inserting data into Microsoft Excel worksheets.
Demonstration
Parameters
General
Use the General page of the Excel Destination component dialog to specify the destination Excel worksheet and options.
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 the destination Excel worksheet. If the specified sheet starts with the colon (:) symbol, the component will treat the remaining definition as a sheet index 2.0.
Specify the destination start column.
Specify the destination start row.
Indicate that the first row contains column names.
Indicate what data does the input contain. This parameter has the options listed in the following table.
Option Description Formatted Value The input contains regular cell data. Formula The input contains cell formula. Indicate that the worksheet is to be cleared before inserting data.
DEPRECATED: Use ExistingData parameter instead.Indicate that the workbook is to be saved at the end.
Indicate how to handle any existing data in the Excel file. This parameter has the options listed in the following table.
Options Description Clear Clear worksheet before inserting data. Overwrite Overwrite existing data with new data. Append Append input data to existing data.
Columns
Use the Columns page to select the columns to be written to the output Excel file and their order.
This lists the columns, by name, that are available to be written to the output Excel file. If you do not make any changes, the columns will be placed in the output Excel file in the order displayed. Input Column shows the order in which the columns will appear in the output Excel file.
To change the order of the columns in the output file, uncheck all the columns in the Available Input Columns list. Then, check the column you would like to appear first. You will see it appear under Input Column and Output Alias. Then, check the column you want to appear second in the output file. Again, you will see the column appear under Input Column and Output Alias. Continue this process until all of the columns you want in the output file are organized in the order you want them to appear.
Shows the current order in which the columns will appear in the output Excel file. This order can be changed by following the procedure described under the Available Input Columns parameter.
The text under Output Alias will be used as the column headers in the output Excel file. The name of any column may be changed by typing over the text in this list.
Error Output
Use the Error Output page to select error handling options.
View the name of the data source.
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 the description of the error.
Knowledge Base
- Where can I find the documentation for the Excel Destination?
- Why does the Excel Destination Plus Component ignore the new values entered into the editor?
- How can I set the Date Format for dates in the Excel Destination?
- Why does my Excel Destination not overwrite the data correctly?
- Can I use Data Flow Task Plus to write data into multiple worksheets in an Excel file?
Related scripts
What's New
- New: Ability to reference sheet by index (:index) when the sheet name is unknown.
- Fixed: Incorrect inclusion of excluded columns in a dynamic data flow.
- Fixed: Failed when multiple instances executed simultaneously against same connection (Thank you, Manasi).
- Fixed: Component UI didn't handle correctly output column modification (Thank you, Daniel).
- Fixed: Component failed to process when used in dynamic data flow (Thank you, Dan).
- New: A new parameter ExistingData specifying what to do with existing data - Clear, Overwrite or Append. The existing Clear parameter is deprecated.
- Fixed: Component failed to process input columns of type DT_TEXT and DT_NTEXT (Thank you, Marcy).
- Fixed: Component failed with "Unable to determine the identity of domain." error, when trying to create larger xlsx file (Thank you, Cory).
- Fixed: Component is now multithread-safe.
- 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.