MSJET 4.0 in 64 bit environment
Question:Way of connecting to xls files from 64 bit sql server.
Answer: There is no direct way of doing this action however we have different ways of workaround this issue since we don’t have JET provider for 64 bit
Here are more details and possible workarounds for this issue:
As I mentioned before, unfortunately Oledb provider for Jet is not available for 64 bit machines. You can find details more about Ole db provider jet discussions.
In order to communicate with Access or Excel files, we need to go through the JET Driver as Access is a JET Database. There is no JET Driver available for the x64 platform. Jet is a deprecated component. Here is the information from the MDAC roadmap available on MSDN (<https://msdn2.microsoft.com/en-us/library/ms810810.aspx>):
Deprecated MDAC Components
These components are still supported in the current release of MDAC, but might be removed in future releases. Microsoft recommends that when you develop new applications, you avoid using these components. Additionally, when you upgrade or modify existing applications, remove any dependency on these components.
• Jet: Starting with version 2.6, MDAC no longer contains Jet components. In other words, MDAC 2.6, 2.7, 2.8, and all future MDAC releases do not contain Microsoft Jet, Microsoft Jet OLE DB Provider, or the ODBC Desktop Database Drivers.
This component is not going to be supported thru 64 bit. It is still available on the 32 bit side going through SysWOW, but would require you to install the 32bit version of SQL Server to access the 32bit components.
https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1243292&SiteID=17
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=611259&SiteID=1
You can find some information about 64 bit support.
You may not be able to connect to as many data sources from a package executing in 64-bit mode as you can from a package executing in 32-bit mode. Some .NET Framework Data Providers and native OLE DB providers may not be available in 64-bit versions. For example, the Microsoft OLE DB Provider for Jet, which connects to Access databases and Excel spreadsheets, is not available in a 64-bit version.
Noted that the Microsoft OLE DB Provider for Jet, used for Access and Excel databases, is not available in a 64-bit version.
Integration Services Considerations on 64-bit Computers
https://msdn.microsoft.com/en-us/library/ms141766(SQL.90).aspx
As a conclusion, this driver is not supported in 64 bit machines.
Possible workarounds
-Here are steps for possible solution for you
1-Install SQL Express to windows 2003 64 bit machine
2-Open surface area configuration tool and choose Surface Area Configuration for Features and go to sql express instance
3-Enable Ad hoc Remote Queries option (Choose Enable Openrowset and opendatasource support)
4-Create a link server to sql express database in your 64 bit sql server called EXPRESS and in security tab, choose “Be mafe using the login’s current security context” option)
5-Create a folder called test and put an xls file in this folder
6-Run following command :
SELECT * FROM OPENQUERY (EXPRESS, 'SELECT * FROM OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'',
''Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0'')...[Customers$]')
You may want to check ways of querying excel files like described in following article:
How to import data from Excel to SQL Server
https://support.microsoft.com/kb/321686
Or as alternative solutions:
-You can use 32 bit sql server
-You may use SSIS Packages to import excel files to sql server and query data from sql server but please take a look to following article to implement this solution
Integration Services Considerations on 64-bit Computers
https://msdn.microsoft.com/en-us/library/ms141766(SQL.90).aspx
*Kagan Arca