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.
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.

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:

  • Premium Excel Field – Column that will be retrieved from 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 according.
        • 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.