Excel Source

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

Download 32 bit

SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

Download 64 bit
All Downloads ----- Join VIP ----- Questions?
Tags: 
1.5, Component, Source, Excel

Overview

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

The component dialog contains the following elements:

  • General tab - specify the source Excel worksheet and region.
  • Columns tab - select the columns to load.
  • Error Output tab - specify error handling options.

Demonstration

    Setup

    Use the parameters below to set up the component.

    General

    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 Excel worksheet as the source.

    Indicate that the first row contains column names.

    Specify a named range within the source Excel file.

    Specify which columns from the source are to be used as the input region.

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

    Specify which rows from the source are to be used as the input region.

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

    Specify the output type. This parameter 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 the Reset button to re-initialize the component metadata.

    Columns

    Use the Columns tab to specify which columns are to be processed from the source Excel file and to specify new names for the columns being processed, if desired.

    This table lists the columns, by name, that are available to be processed from the source Excel file. If there are columns that you do not want to process, uncheck them from the list.

    You cannot use this table to add or delete columns in the source Excel file. To manipulate data in an external Excel file, use the COZYROC Excel Task.

    Shows the columns that will be processed from the source Excel file based on the columns selected in the Available Input Columns list.

    This shows the column name that will be used during processing. The name of any column may be changed by typing over the text in this list. Each name must be unique.

    Error Output

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

    View the name of the data source.

    View the selected source column.

    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 a 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 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 Source Plus].[Worksheet]. This is the property containing the worksheet.
    • Scroll down and find the 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 a comma (,).
    • Scroll down and find the Expressions property. Set up an expression to modify the worksheet or region dynamically.

    Samples

    • Excel data sources: no longer the scourge of SSIS Developers by Daniel Perianez.

    Related documentation