Excel connection manager Error while running a package on a server

Hi experts, I have a problem where my package runs on my local drive but when I put it on a server where excel is not installed, it throws an error, not sure what the workaround is, the package here that I have creates an excel file with a tab and headers and later dataflow task to export data to excel. I have had packages created on the same server where I had data imported from excel files but I dont understand why we cant do it the other way....
Appreciate your help...

ERROR MESSAGE[Execute SQL Task] Error: Failed to acquire connection "Excel Connection Manager". Connection may not be configured correctly or you may not have the right permissions on this connection.
sqlcuriousAsked:
Who is Participating?
 
BartVxCommented:
I can think of two possible causes:

1) the user running the package does not have sufficient access rights on the Excel file.

2) You are running the package in 64 bit mode on the server. The OLEDB provider to connect to Excel files is not available in 64 bit.
Check the "Running integration services packages on 64 bit computers" section in following link for more information about running packages in 32/64 bit mode:

http://msdn.microsoft.com/en-us/library/ms141766.aspx


0
 
sqlcuriousAuthor Commented:
Hi Bartvx, its not the case 1 and regarding the option 2- I have already turned off the 62 bit option. There is something else that's happening
Thanks,
0
 
BartVxCommented:
Are you trying to connect to Excel 2007? If so, install the Office 2007 Data Connectivity Components from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734
 on the server.

If this is not the case, let's make sure the 64 bit issue is not the problem:

If you mean the Run64BitRuntime  option in the debugger options: Thi option only applies at design time and will have no effect when you execute the package on the server.

If you launch 'dtexec' command  in a command prompt without parameters on the server, what can you see in the version section?

If it says 64 bit, try to locate the 32 bit version of dtexec (typically in C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn. (note the X86 part) Then try to execute the package explicitly specifying the path to the 32 bit version.

ex: "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec" /f c:\mypackage.dtsx

Some more questions to make finding the solution easier:
What version of SQL are we talking about?
What are the versions of dtexec on your local PC and on the server?
What provider do you use to connect to Excel?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
BartVxCommented:
Addendum: for Excel 2010 the installation of Microsoft Access Database Engine is required, you can download it here:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255

0
 
sqlcuriousAuthor Commented:
I am not allowed to install any office products so the solutions above wouldnt work, please provide me alternative solutions.
Hi Bartvx answers to ur questions:
What version of SQL are we talking about?  - SQL server 2008 R2
What are the versions of dtexec on your local PC and on the server? both are the same
What provider do you use to connect to Excel? - excel connection manager

0
 
BartVxCommented:
As far as I know there is no way to connect without those drivers installed. :(

The only other option that I see is that you run the package on another computer (with connection to the database on the target server) where you are allowed to install the Office drivers.

Note that the links I posted are not the actual Office products, but merely the office connectivity tools which are free to download.
0
 
Anthony PerkinsCommented:
>>I am not allowed to install any office products<<
Thank goodness for that.  Suggesting you install MS Office on a Production SQL Server is dangerous at best.
0
 
sqlcuriousAuthor Commented:
k got it, I got confused, will try the above options and let you know if I come across any hiccups, thanks Bartvx!
0
 
sqlcuriousAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.