SSIS, SQL 2012 and openrowset

Question

SSIS, SQL 2012 and openrowset

Helen Dyakonova

SSC-Addicted

Points: 465
More actions
September 20, 2012 at 12:09 am

#261820

I have a sp that executes openrowset statement dynamically. We were able to make it work when execute it in Management Studio in 2012 64 bit but still have problems when execute sp via SSIS package. It gives the same error:
Executing the query "EXEC dbo.sp_bufXLImportPopulate ?" failed with the following error: "Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I've used profiler and tracked sql statement that goes to the server. It is run using the same login as in Management Studio and if I executes this statement it works perfectly but fails in SSIS.
Please, help. I simply don't know what to look at.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ravi Chhabria Ten Centuries Points: 1020 More actions · Answer 1

Not sure if this is good question to ask but still; are you running SP from management studio and SSIS package on same database or on different copies of databases?

The reason I am asking this is, from the error it seems "Microsoft.ACE.OLEDB.12.0" excel driver might be missing on the server on which database being used from SSIS package is installed.

Helen Dyakonova SSC-Addicted Points: 465 More actions · Answer 2

Helen Dyakonova

SSC-Addicted

Points: 465

September 20, 2012 at 6:20 am

#1540021

Same database, same server, same user.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Same file location?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Helen Dyakonova SSC-Addicted Points: 465 More actions · Answer 4

Helen Dyakonova

SSC-Addicted

Points: 465

September 20, 2012 at 12:29 pm

#1540305

opc.three (9/20/2012)
Same file location?

Sure.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

What is running your SSIS package? A job?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Helen Dyakonova SSC-Addicted Points: 465 More actions · Answer 6

opc.three (9/20/2012)
What is running your SSIS package? A job?

Manually from Business Intellegence Studio.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

How are you connecting to the DB instance, with Windows Auth or SQL Auth?

And just to confirm, you're opening SSMS and BIDS on the same machine using the same creds to connect to the DB instance?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Helen Dyakonova SSC-Addicted Points: 465 More actions · Answer 8

opc.three (9/20/2012)
How are you connecting to the DB instance, with Windows Auth or SQL Auth?
And just to confirm, you're opening SSMS and BIDS on the same machine using the same creds to connect to the DB instance?

SQL Auth and yes, the same machine and same cred.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

The info we've brought out on the thread so far does not compute. There is some other variable affecting the behavior that is missing. I'll step aside and see if someone else has any ideas.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sharathreddy_s4 Valued Member Points: 73 More actions · Answer 10

i had the same issue now.. i think you might had a fix by now but just wanted to let you know what i found.. i just reopened visual studio (BIDS) by Run as Administrator option and the package ran fine for me. The driver is not initiliazing when i run as normal user in visual studio.