Excel Destination

Overview

Excel Destination Component is SSIS Data Flow Component for inserting data into Microsoft Excel worksheets.

The component dialog contains the following elements:

  • General tab - specify destination Excel worksheet and options.
  • Columns tab - select columns to insert.
  • Error Output tab - specify error handling options.

Setup

Use the options below to setup the component.

General

Connection

Select CozyRoc Excel connection manager.

Related Topics: Excel Connection Manager

Worksheet

Select destination Excel worksheet.

Start Column

Specify destination start column.

Start Row

Specify destination start row.

First row has column names

Specify first row contains column names.

Clear

Specify to clear worksheet before inserting data.

Save At End

Specify to save workbook at the end.

Columns

Use the Columns page to select output columns.

Input Column

View input (source) columns in the order in which the task will write them. You can change this order by first clearing the selected columns in the table discussed above, and then selecting input columns from the list in a different order.

Output Alias

Provide a unique name for each output column. The default is the name of the selected input (source) column.

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 destination dynamically at runtime?

The destination worksheet and start columns and rows are available in the data flow properties list. Follow these steps to setup expression:

  • Right-click on data flow canvas and select Properties menu.
  • Scroll-down and find property named like [Excel Destination Plus].[Worksheet]. This is the property containing the destination worksheet.
  • Scroll-down and find property named like [Excel Destination Plus].[StartColumn] or [Excel Destination Plus].[StartRow]. These are the properties containing the destination column and row.
  • Scroll-down and find Expressions property. Setup expression to modify the worksheet or region dynamically.