I am working with SSIS to import an Excel file into SQL Server. Not being an SSIS expert, I usually use the import wizard in SSMS which lets me create the destination table, load the data, and save the package to the file system. Then I will review the wizard-created table, modify any data types as needed, and then generate a proper CREATE TABLE statement.
In some cases however, the wizard does not assign the correct data types, and the saved package does not work - one or more columns will be blank. This is caused by the baked-in behavior of Excel, where it examines the beginning of the data & guesses the data types. If the first few rows are not fully populated with the appropriate data, that's a problem.
The fix for this is remarkably simple, but took me lots of Googling to find the solution Import Excel in SSIS and get Null values in stead of Numeric values posted by Kasper de Jonge...
When you open the package in SSIS, in the Excel connection manager you simply add "IMEX=1" so it looks this:
Excel 8.0;HDR=YES;IMEX=1
which forces SSIS to read the Excel file using the data types you specify in the package.
