Excel Destination

Overview

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.

Setup

Use the parameters below to set up the component.

General

Connection

Select the COZYROC Excel connection manager. Note: This will not work if you select the standard Excel connection manager.

Related Topics: Excel Connection Manager

Worksheet

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.

Input

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.
Clear

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.

Columns

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.

Error Output

Use the Error Output page to select error handling options.

Input or Output

View the name of the data source.

Error

Specify what should happen when an error occurs: ignore the failure, redirect the row, or fail the component.

Related Topics: Handling Errors in Data

Truncation

Specify what should happen when a truncation occurs: ignore the failure, redirect the row, or fail the component.

Description

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.

Related scripts