Oft times trying to use the SQL SSIS Import wizard to load data into SQL server from excel you run into data type conversion errors and have to go through many machinations to alter the offending column data types and error / truncation handling to get the file loaded.

 

Wes Arnold showed me a trick that he uses which significantly speeds up the process. It is a 2 step process:

  1. Import the excel file into Access since it is much more forgiving about the data type translations
  2. Import the access data into SQL server using the import tool.

First, import the excel file into an access database. This handles the import much better.

AccessImportExcel

Select the source file to load into a new table

Access1

Select the worksheet to load and confirm the structure looks like what you expect.

Access2

Confirm whether or not the first row has headings

Access3

Make any column or data type adjustments. You should be able to just click next on most files. Additionally, you can ignore certain columns at this point as well if you do not want to load them.

Access4

Specify your primary key choice.

Access5

Name the destination table in access

Access6

You should now have your data in access

Access7

Save the Database as an Access 2000 .mdb file

Access8

Now use the SQL SSIS Import wizard to take the data from access and put it into SQL Server.

SSMSImport

This process is much easier than modifying all of the column data types and error handling in the SQL SSIS Import wizard.

 

Are there any tips and tricks you use?

Let us know in the comments below.