SSIS 2012 and Excel

Question

SSIS 2012 and Excel

Kwisatz78

SSChampion

Points: 12478
More actions
March 7, 2014 at 3:51 am

#304910

Hi
I am using VS2012 and creating a package on a 64bit machine to import some data from a .xlsx file. My question is that I am getting an error for the Excel connection manager, do I need to install some kind of excel drive or excel itself on the machine in order to be able to import the data?
Thanks

Viewing 7 posts - 1 through 7 (of 7 total)

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

I don't believe there is a 64 bit JET driver for Excel.

You need to run the package in the 32 bit runtime. In Visual Studio, this is done through the debugging properties of the project. When scheduling in SQL Agent, there's a tick box for this under execution options when setting up the job.

Kwisatz78 SSChampion Points: 12478 More actions · Answer 2

Hi - yes I have found a property called Run64BitRunTime and set it to false however I still get the same error, which is was is leading me to believe I have some Excel component missing on my server preventing the connection manage from reading the Excel file.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 3

Kwisatz78 (3/7/2014)
Hi - yes I have found a property called Run64BitRunTime and set it to false however I still get the same error, which is was is leading me to believe I have some Excel component missing on my server preventing the connection manage from reading the Excel file.

Maybe if you posted the error, that would be helpful.

Are we talking about .xls or .xlsx files?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Kwisatz78 SSChampion Points: 12478 More actions · Answer 4

Apologies - the error is:

Could not retrieve the table information for the connection manager 'Excel Connection Manager'.

Failed to connect to the source using the connection manager 'Excel Connection Manager'

and I am trying to import an xlsx file.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 5

You need to make sure the 32-bit ACE OLE DB provider is installed on the machine.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Kwisatz78 SSChampion Points: 12478 More actions · Answer 6

Thats fixed it thanks, I had installed the 64bit ones but that hadn't worked.

Thanks again