Determine the datatype for excel import

Introduction 

Determine the datatype of a column in a Excel file in SSIS

Background 

Whenever we are working with SISS for the task of importing rows from a Excel and saving it to table with our without any manipulation in database, SSIS does type guess and determines what will be the datatype for the column. 

In general based on few early rows of a column SSIS fix the data-type and same datatype is consider for all the remaining  rows, by default it is set to 8 rows by Windows. When the case arise where we have initial few rows as empty or null followed by numeric values, SSIS consider it as string datatype and create issue when it is mapped to any Integer type of column of a table in SSIS.

To overcome it we can increase the typeguess row size in the windows registry and so that data-type will be considered after tracing the rows of specified number of rows.

 It is available in following path:

 regedit --> HKEY_LOCAL_MACHINE --> SOFTWARE --> Microsoft -->  Jet -> 4.0 --> Excel

Double click on the TypeGuessRows property , select the Decimal in Base and type the number of rows that need to be traced before determing the datatype.