Help Manual - Excel Source

Documentation on SSIS Excel Source component

Excel Source Component

The 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

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

SSIS Premium Excel Source Component

Source File Settings
Connection Manager

The 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 for your current SSIS packages. The supported connection managers are listed below:

  • Excel Connection Manager
  • Local File
  • SFTP Connection Manager
  • FTPS Connection Manager
  • Amazon S3 Connection Manager
  • Azure Blob Connection Manager
  • Azure Data Lake Storage Connection Manager
  • Azure Files Connection Manager(since v20.1)
  • Box Connection Manager
  • Dropbox Connection Manager
  • Google Cloud Storage Connection Manager
  • Google Drive Connection Manager(since v21.2)
  • Hadoop Connection Manager
  • OneDrive Connection Manager
  • SharePoint Connection Manager (offered with the SSIS Integration Toolkit for Microsoft SharePoint)
  • WebDAV Connection Manager(since v20.1)
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 the local disk and use Excel Connection Manager.

Configure Source
Worksheets Names

Specify the Excel WorksheetName you want to work with.

Worksheets Index

Specify the Excel WorksheetIndex 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 checked, 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 the Named Ranges source is selected, the 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 updates of the property at run time.

Generate Documentation Button

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

Columns Page

The Columns page of the Excel Source Component shows you all available attributes 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.
    • Ignore Cast exception: This option can be set to True or False.
    • 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.
    • Add Hyperlink Column: This button can be used when the field you are trying to read has hyperlink information which in this case, the component will create an additional field called fieldname_hyperlink which will include the link for a given text.
    • Apply this output format to all fields: Click this button to apply the current Output Format to all fields in the source.
    • Import External Columns: Click this to import external columns from a JSON file.
    • Export External Columns: Click this button to export the columns to a JSON file.
  • + sign: Add field to Excel File.
  • - sign: Remove field from Excel File.
  • Arrows: Move the fields to a desired location in the file.