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:
- Import the excel file into Access since it is much more forgiving about the data type translations
- 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.
Select the source file to load into a new table
Select the worksheet to load and confirm the structure looks like what you expect.
Confirm whether or not the first row has headings
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.
Specify your primary key choice.
Name the destination table in access
You should now have your data in access
Save the Database as an Access 2000 .mdb file
Now use the SQL SSIS Import wizard to take the data from access and put it into SQL Server.
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.
