HI Experts , 

I am getting very much confused with 64 bit ,31 bit environment in SQL server, SSIS and MS office , Accessdatabase engine
which I want to use for ETL as what should be the combination


My scenario - I have written an SP with  OPENrowset to be used  in EXECUTE SQL TASK and load data into SQL Table
example 

SELECT * into myTable 
     FROM OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=E:\SourceFolder\Department.xlsx;HDR=YES;IMEX=1',
    'SELECT * FROM [Sheet1$]');



My SQL SERVER version is :: 

Microsoft SQL Server 2012 - 11.0.2316.0 (X64) 

Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


MS OFFICE 2010, 64 Bit 

After executing 

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

My OPENROWset query Store Procedure is working fine in SSMS but when I executed Same SP in EXECUTE SQL TASK ,it failed .
Also I was not able to used Excel as a Source , I forgot the error but it did not even allowed me to select a sheet Name in Excel Source..

After doing bit of google I found that we need to have MS office 2010 in 32 bit because our BIDS is 32 Bit .


I asked my admin team to uninstall MS office 2010 64 bit and Install 32 bit version.

Now my versions are 
SQL server 2012 , 64 bit 
MS office 32 bit 

Now

1) I am able to Take excel sheets as Source in SSIS Excel Source transformation but package fail 

2) SP with OPENROWSET Query in which was running fine earlier in SSMS  is also throwing error

3) and Execute SQL TASK with same SP in SSIS are  throwing error ..

NOW I Installed AccessDatabaseEngine i.e. 32-bit but same issue 

"The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server"


PLEASE TELL ME THE COMBINATION .. I cannot reinstall SQL SERVER but definately MS OFFICE VERSION AND ACCESSdatabase engine version

I WANT 

1) MY OPENROW SET QUERY CAN RUN BOTH IN SSMS and Execute SQL Task (SSIS).
2) I can use Excel as a Source in SSIS Package (with out making RUN64bitRUNTIME to FALSE )

PLEASE ASSIST

How do you run the package?

Bear in mind the RUN64bitRUNTIME setting does not apply to packages run outside the SSDT or Visual Studio

 Hi Arthur,

At Present I am running package in SSDT , once package is completed I will schedule a Job (SQL Agent)to execute it .

You need to use 32 bit compoent itself in SSDT as its a 32 bit application. After doing development you can make it point to 64 bit driver to execute it from job. Alternatively you can use 32 bit version itself and set 32 bit runtime to true in job properties

Make sure you read this

https://technet.microsoft.com/en-us/library/ms141766(v=sql.105).aspx

Hi Visakh , 

I still get an error while running 

SELECT * --INTo productlist
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=D:\Mushtaq\ExcelS\Dept.xlsx',
    'SELECT * FROM [S$]'
    )

both IN SSMS and Execute SQL Task(Even after making Runas64bit to FALSE) ..

[Execute SQL Task] Error: Executing the query "SELECT * 
INTO productlist
 FROM OPENROWSET
('Micro..." failed with the following error: 
"OLE DB provider 'Microsoft.ACE.OLEDB.12.0' 
cannot be used for distributed queries because the provider is configured 
to run in single-threaded apartment mode.". 
Possible failure reasons: Problems with the query, 
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have also executed below Query in SSMS but no luck:

USE [master]
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
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

But NOO Luck :(

After reading your reply and link that you have forwarded I need to know what is wrong or missing in my Current environment to execute them. 

My Environment.

1)SQL SERVER 2012 64 Bit

2)MS office 32 Bit

3)MicrosoftAccessDataBase Engine 2010 -32 Bit .. 

Please assist..

• Edited by Rihan8585 7 hours 51 minutes ago

Hi Visakh , 

I might sound stupid asking almost same things again even after reading the Link but thing are not very clear with me and I am  still getting an error while running 

SELECT * --INTo productlist
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=D:\Mushtaq\ExcelS\Dept.xlsx',
    'SELECT * FROM [S$]'
    )

both IN SSMS and Execute SQL Task(Even after making Runas64bit to FALSE) ..

[Execute SQL Task] Error: Executing the query "SELECT * 
INTO productlist
 FROM OPENROWSET
('Micro..." failed with the following error: 
"OLE DB provider 'Microsoft.ACE.OLEDB.12.0' 
cannot be used for distributed queries because the provider is configured 
to run in single-threaded apartment mode.". 
Possible failure reasons: Problems with the query, 
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have also executed below Query in SSMS but no luck:

USE [master]
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
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

But NOO Luck :(

I need to know what is wrong or missing in my Current environment to execute them in SSMS and SSIS

My Environment.

1)SQL SERVER 2012 64 Bit

2)MS office 32 Bit

3)MicrosoftAccessDataBase Engine 2010 -32 Bit .. 

Please assist..

• Edited by Rihan8585 15 hours 23 minutes ago

https://social.msdn.microsoft.com/Forums/en-US/1d5c04c7-157f-4955-a14b-41d912d50a64/how-to-fix-error-the-microsoftaceoledb120-provider-is-not-registered-on-the-local-machine?forum=vstsdb