Basically, SSMS is a 32-bit application even when we install 64 bit SQL Installation. When we launch the SQL Server Import and Export wizard (DTSWizard) it runs the 32-bit version of it which needs the 32-bit version of Office libraries. if we have installed a 64-bit office then these office libraries will not talk to each other so we have to match the compatibility.
Anyway, there is a 64-bit version of DTSWizard in the installation folder/menu, we can use this externally from the Start menu.
Follow the below steps to import data from Excel sheet to SQL Server
- Open DTSWizard from Start Menu
- Choose a Data Source from Data Source list –> Select Microsoft Excel and select excel file
- Choose a Destination –> Select SQL Server Native Client and provide authentication details
- Specify Table Copy or Query and Finish
/Siva