Import Excel into SQL 2008 64 bit

Last couple days, I was working on a solution to import some excel file into SQL and then generate report from the server.

There're a lot of ways to import an excel file, I tried a few approaches, unfortunately due to lack of permission to muck around with server settings in the company, I was unable to proceed with most of these approaches.

Approach 1 : (ISSUE: 32 bit office installed, 64bit SQL installed)

Step 1. Install 64-bit Microsoft.ACE.OLEDB.12.0 driver  (Failed on this one, Don't have server admin rights to install 64 bit version drivers.)

Microsoft Access Database Engine 2010 Redistributable

Step 2. Configure ACE OLE DB properties

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

Step 3. Configure linked server using Microsoft.ACE.OLEDB.12.0 provider

SELECT * INTO #ExcelTemp FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=V:\database\Export\Service KPI Report 2013.xls','select * from [2013$]')

I did run step 2&3 on my local machine (XP sp3, 32bit SQL 2008 R2), work like a charm.
Possible issues:
you may come across:

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

This will fix it :

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

Also this error is common:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Fixes:

USE [master]

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1

GO

-------------------------------SEPERATOR ------------------------------------------------------------

Approach 2: LINKED Server
Same issue with the first approach, 32 bit office and 64 bit SQL. on all 32 bit servers, everything goes smoothly, where as 64 bit SQL will have errors and not be able to proceed.

EXEC sp_addlinkedserver
    @server = 'ExcelServer',
    @srvproduct = 'Excel', 
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\Test\test.xlsx',
    @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'

-------------------------------SEPERATOR ------------------------------------------------------------

Approach 3: SSIS

Easy enough, end up doing this.