?
Solved

Excel connection manager Error while running a package on a server

Posted on 2011-10-03
9
Medium Priority
?
296 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:sqlcurious
  • 4
  • 4
9 Comments
 
LVL 3

Accepted Solution

by:
BartVx earned 2000 total points
ID: 36908247
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
 

Author Comment

by:sqlcurious
ID: 36921094
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
 
LVL 3

Assisted Solution

by:BartVx
BartVx earned 2000 total points
ID: 36923181
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 3

Expert Comment

by:BartVx
ID: 36923414
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
 

Author Comment

by:sqlcurious
ID: 36944923
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
 
LVL 3

Expert Comment

by:BartVx
ID: 36947551
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36949238
>>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
 

Author Comment

by:sqlcurious
ID: 36949828
k got it, I got confused, will try the above options and let you know if I come across any hiccups, thanks Bartvx!
0
 

Author Closing Comment

by:sqlcurious
ID: 36952340
Thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question