Importing Excel files to SQL Server is a frequent task you need to carry out in your day to day life. Most probably you are using SSIS to import Excel files to SQL Server.
If you are asked to import an Excel file, you can use Excel Source from the Data Flow Sources SQL Server Integration Services (SSIS) and select correct version from the available list.
You can see that you can only import Excel files up to Microsoft Excel 97-2005 version, which means that you are not allow to import Excel 2007 files from the above control.
However, if you follow below steps, you can import Excel 2007 files into the SQL Server.
1. Drag and drop OLE DB Source data flow source to the data flow task.
2. Double click the OLE DB Source and click New button for OLE DB Connection Manager.
3. Click New button in the in Configure OLE DB Connection Manager screen.
4. Select Native OLE DBMicrosoft Office 12.0 Access Database Engine OLE DB Provider from the OLE DB Provider list.
5. Select All option and at the Extended Properties enter Excel 12.0. After this you will see a screen like following image.
You can see the selected provider at the top of the screen.
6. Enter the file name with full path and make sure you have the extension xlsx.
7. After clicking OK button, you will be taken to the initial screen, in which you have to select the worksheet you want.
8. After this, you are ready to import data from Excel 2007 to SQL Server. It is just a matter of inserting correct destination you need.
]]>