December 24, 2009 at 8:53 am
I get the following error when running an SSIS-2008 package from a SQL-Agent job (I have confirmed that the SQL-Agent account can access the location where the XLS file is located):
The Microsoft Jet database engine cannot open the file '...\xxxxxx.xls'. It is already opened exclusively by another user, or you need permission to view its data.".
In the BIDS solution the connection is made with the "Microsoft.Jet.OLEDB.4.0" provider.
I wonder if I am missing this provider in the machine where the job is running.
How can I find out? Control Panel --> Add/Remove Programs?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 24, 2009 at 9:33 am
Marios Philippopoulos (12/24/2009)
I get the following error when running an SSIS-2008 package from a SQL-Agent job (I have confirmed that the SQL-Agent account can access the location where the XLS file is located):The Microsoft Jet database engine cannot open the file '...\xxxxxx.xls'. It is already opened exclusively by another user, or you need permission to view its data.".
In the BIDS solution the connection is made with the "Microsoft.Jet.OLEDB.4.0" provider.
I wonder if I am missing this provider in the machine where the job is running.
How can I find out? Control Panel --> Add/Remove Programs?
I don't understand what you mean by that but here are the default account information for the Agent, a limited account will not work if SSIS and the Agent are in different servers.
http://msdn.microsoft.com/en-us/library/ms345380.aspx
Kind regards,
Gift Peddie
December 24, 2009 at 4:18 pm
I've read that Access & Excel OLE DB drivers will not work on 64 bit version of SQL Server. Could that be it? Hope this helps.
.
December 29, 2009 at 1:54 pm
BSavoie (12/24/2009)
I've read that Access & Excel OLE DB drivers will not work on 64 bit version of SQL Server. Could that be it? Hope this helps.
It seems so, and thanks for raising this issue. I forgot to mention the very important point in my OP that I am working on 64-bit platform.
I downloaded and attempted to install the Jet-4.0 driver from this link:
Jet 4.0 Service Pack 8 (SP8) for Windows Server 2003 64 Bit Edition (KB829558):
During installation I got the following error:
The image file update\update.exe is valid, but is for a machine type other than the current machine.
After some investigation of this error on the web, it seems the exe I downloaded is for IA64, ie. the Itanium platform.
Not sure why this is not made clear in the download link above...
Anyway, I wonder whether there is a driver suitable for 64-bit non-Itanium systems.
That does not appear to be the case.
I will try my luck with the 32-bit variety for now:
http://support.microsoft.com/kb/239114
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 29, 2009 at 2:13 pm
When I try to install windowsserver2003-kb829558-x86-enu.exe on my 64-bit server, I get the following error:
This kb829558 is for a different hardware platform.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 29, 2009 at 2:19 pm
There is no JET driver for either Itanium or X64 so is a working solution to run SSIS package with Excel.
Kind regards,
Gift Peddie
December 29, 2009 at 2:25 pm
Marios Philippopoulos (12/29/2009)
When I try to install windowsserver2003-kb829558-x86-enu.exe on my 64-bit server, I get the following error:This kb829558 is for a different hardware platform.
To use most x86 tools you need to install the x64 which will install both but I have posted an existing solution.
Kind regards,
Gift Peddie
December 29, 2009 at 2:30 pm
Gift Peddie (12/29/2009)
There is no JET driver for either Itanium or X64 so is a working solution to run SSIS package with Excel.
Thank you for the link, it looks like this is exactly what I need!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 31, 2009 at 11:05 am
I was able to fix the issue of incompatibility of the Jet Engine with the 64-bit platform by checking the "Use 32-bit runtime" checkbox under Execution Options in the SQL Agent job.
After doing so, my job ran successfully.
Incidentally: there was originally a "-" character in the name of my EXCEL connection manager that was causing an error (something to the effect of invalid argument for Connection option); after removing the "-" character from the name, that error was gone. It seems the SSIS engine does not like connection managers/data sources with "-" in their names... Who knew?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply