Excel Source

Overview

Excel Source Component is SSIS Data Flow Component for consuming data from Excel worksheets.

The component dialog contains the following elements:

  • General tab - specify source Excel worksheet and region.
  • Columns tab - select columns to load.
  • 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 source Excel worksheet.

First row has column names

Specify first row contains column names.

Named Range (1.5 SR-2)

Select source named range.

Region Column

Specify source region column information.

  • Start - start column. The column is 1-based.
  • Width - number of columns.
Region Row

Specify source region row information.

  • Start - start row. The row is 1-based.
  • Height - number of rows. If set to -1, the component will read all used rows from worksheet.
Output

Specify output type. This property has the options listed in the following table.

Value Description
Formatted Value Output formatted cell value.
Raw Value Output raw cell value.
Formula Output cell formula.
Reset

Click Reset button to reinitialize component metadata.

Columns

Use the Columns page to map an output column to external (source) column.

Available External Columns

View the list of available external columns in the data source. You cannot use this table to add or delete columns.

External Column

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

Output Column

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

Error Output

Use the Error Output page to select columns error handling options.

Input or Output

View the name of the data source.

Column

View selected source column.

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

The source worksheet and region 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 Source Plus].[Worksheet]. This is the property containing the worksheet.
  • Scroll-down and find property named like [Excel Source Plus].[Region]. This is the property containing the source region. The property is a string combination containing values for start column, start row, width and height. Each value is separated with comma (,).
  • Scroll-down and find Expressions property. Setup expression to modify the worksheet or region dynamically.