This is actually an old issue but I recently found myself struggling to find a solution because I had forgotten what I had done the last time. I don’t normally use the import export wizard within SQL server management studio I prefer to use visual studio and create SSIS packages (where I do the transformations within the package itself). However, this was a onetime activity and I didn’t want to go through the effort of creating an entire SSIS package and figured that the easiest solution would be to simply use the import export wizard within management studio.
What I had forgotten was in recent versions of Management Studio the 32-bit providers for Microsoft Oledb aren’t provided or supported. Which means that if you are using a 64-bit edition of Microsoft office you would need to install 64-bit edition of OLEDB drivers. Management studio doesn’t seem to recognise these 64-bit editors within the SSMS UI itself. In such cases you will find that when you try to import the Excel sheet using SSMS you will get the below error.
The easiest thing to do initially and the correct approach in previous versions was to download the appropriate OLEDB provider from the Microsoft website. This is no longer possible with newer editions of Microsoft SQL Server Management Studio. Therefore, what we need to do is first of all check whether we have actually installed these drivers and if they aren’t installed I would recommend that you go ahead and download and install them anyway. If these drivers are already installed which you can find within control panel program files. Then you need to go ahead and search for import-export 64-bit edition wizard in the start menu as shown in screenshot below.
This is a separate 64-bit version of the import export wizard. The rest of the steps remain the same. As you would normally use with the 32 bit version.