Premium Excel Source Component

The Premium Excel Source Component is an SSIS data flow pipeline component that can be used to read/retrieve data from an Excel file.

The component includes the following two pages to configure how you want to read data.

  • General
  • Columns

General page

The General page of the Premium Excel Source component allows you to specify the general settings of the component.

SSIS Premium Excel Source Component

Connection Manager

The Premium Excel Source component requires a connection in order to connect to the Excel File. The Connection Manager drop-down will show a list of all connection managers that are available to your current SSIS packages. The supported connection manager are listed as below:

  • Excel Connection Manager
  • Local File
  • SFTP Connection Manager
  • FTPS Connection Manager
  • Amazon S3 Connection Manager
  • Azure Blob Storage Connection Manager
  • Box Connection Manager
  • Dropbox Connection Manager
  • OneDrive Connection Manager
  • SharePoint Connection Manager (available in SSIS Integration Toolkit for Microsoft SharePoint)
Source File Path

The Source File Path specifies the location of the excel file that you are trying to read from. Click the eclipse button ('...') to open up a browser dialog to select an item.

Password to open

This option is used to specify the password to open the Excel file. If the excel file is not encrypted, you can leave this field blank.

Note: Password will be stored as plain text; if this information is critical, please save the file on local disk and use Excel Connection Manager.

Worksheets

Specify the Excel Worksheet you want to work with.

Named Ranges

Specify the Named Ranges defined in the Excel file.

Start Row

Use the Start Row to indicate which row to start from.

Number of Rows

Specify how many rows you wish to retrieve.

Read to End

When this option is checked, the component will read all rows from the Start Row you have specified to the last row of the sheet. Once this option is check, the Number of Rows will be greyed out.

Column Names

There are four options that you can use to indicate Column Names:

  • Not specified: Choose this option if the sheet does not contain column names
  • First row of sheet: Choose this option if the first row contains column names
  • Start row: Choose this option to specify the column name based on the Start Row
  • First row of named range: This option is only available when Named Ranges source is selected, first row in the named range will be used as column names.
Refresh Component

By clicking the Refresh Component button, the component will retrieve the latest metadata from the Excel File you have specified in the Excel Connection Manager. After clicking this button, you will receive a status message indicating how many fields have been updated, added, or deleted.

Expression fx Button

Clicking the fx button to launch SSIS Expression Editor to enable dynamic update of the property at run time.

Generate Documentation Button

Clicking the Generate Documentation button to generate a Word document which describes the component's metadata including relevant mapping, and so on.

Column page

The Columns page of the Premium Excel Source Component shows you all available attribute from the source that you specified on the General page.

SSIS Premium Excel Source Component - Columns Page

On the top left of the grid, you can see a checkbox, which can be used to toggle the selection of all available fields. This is a productive way to check or uncheck all available fields.

The Columns Page grid consists of:

  • Excel Field: Column that will be retrieved from the Excel File.
  • Data Type: The data type of this field.
  • Properties window for the field listed:
    • Name: specify the column name.
    • Output format
      • Calculated with format: return values in the format defined in Excel.
      • Calculated without format: return the value itself without the cell format.
      • Formula: return the formula defined in Excel, which can contain any or all of the following: functions, references, constants operators.
    • Data type: the data type can be changed accordingly.
    • Length: Specify the Length of the fields. If the data type specified is a string, the length specified here would be the maximum size. If the data type is not a string, the length will be ignored.
    • Precision: Specify the number of digits in a number.
    • Scale: Specify the number of digits to the right of the decimal point in a number.
    • CodePage: Specify the Code Page of the field.
    • Apply this output format to all fields: Click this button to apply the current Output Format to all fields in the source.
  • + sign: Add field to Excel File.
  • - sign: Remove field from Excel File.
  • Arrows: Move the fields to a desired location in the file.