The Excel Destination Component is an SSIS Data Flow Component for inserting data into Microsoft Excel worksheets.
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.
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.
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.
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.
The destination worksheet, start columns and rows are available in the data flow properties list. Follow these steps to set up an expression:
- Right-click on the data flow canvas and select the Properties menu.
- Scroll down and find the property named like [Excel Destination Plus].[Worksheet]. This is the property containing the destination worksheet.
- Scroll down and find the property named like [Excel Destination Plus].[StartColumn] or [Excel Destination Plus].[StartRow]. These are the properties containing the destination column and row, respectively.
- Scroll down and find the Expressions property. Set up an expression to modify the worksheet or region dynamically.
- 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.
Ready to give it a try?
COZYROC SSIS+ Component Suite is free for testing in your development environment.