As pointed out earlier, however, before you can configure any options in the Excel Source component, you must create your connection manager to the Excel workbook.Only then can you configure the access mode, column setup, and error handling.
As a consequence of this approach, all non-numerical values are returned as NULL values. Imagine you have a column that’s a mix of numbers and text, and you want the DT_WSTR data type to be used, but if the first eight rows contain four or more numerical values, you’ll get DT_R8 whether you like it or not.
One way to address this issue is to update the column in the worksheet so that it is configured as Text.
SSIS always generates the data type for you when it validates the data based on the first eight rows.
Figure 5: Verifying the data types in the source component’s advanced editor If a column contains mixed data types, SSIS often takes a “majority rules” approach when assigning the type.
Figure 3: The Columns page of the Excel Source Editor dialog box The column names retrieved from the spreadsheet are considered the external columns.