The Excel Destination Component is an SSIS Data Flow Component for inserting data into Microsoft Excel worksheets.
The component dialog contains the following elements:
- General tab - specify the destination Excel worksheet and options.
- Columns tab – select the columns to insert.
- Error Output tab - specify error handling options.
Use the parameters below to set up the component.
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.
- Start Column
Specify the destination start column.
- Start Row
Specify the destination start row.
- First row has column names
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.
DEPRECATED: Use ExistingData parameter instead.
Indicate that the worksheet is to be cleared before inserting data.
- Save At End
Indicate that the workbook is to be saved at the end.
- ExistingData (1.6 SR-1)
Indicate how to indicate the existing data. 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.
- Available Input Columns
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.
- Input Column
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.
- Output Alias
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.
- Input or Output
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.
How to modify the destination dynamically at runtime
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.
- Date Format Transformation script - Convert date/time columns into text representation.