We have a process that we run to import parts into our inventory database. Most of the files we get are XLXS which work as expected, but there is one vendor that for whatever reason sends XLS files. We have the following code in a stored procedure that pulls the file into temp table:
if object_id('tempdb.dbo.#ins_hw_and_sw') is not null drop table #ins_hw_and_sw
create table #ins_hw_and_sw
[Product Type] varchar(600),
[Product Sub group] varchar(600),
[Countries of Origin] varchar(600),
[Country Names] varchar(600),
select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=T:\VENDR_GPL_RAW\ins_RAW.xls;',
FROM [HW AND SW$]')
Our problem is this: If I run the code above in SSMS manually, either by itself or by running the stored procedure, it works just fine. However, if I run it as part of a scheduled job, it fails with an error saying "The Microsoft Access database engine cannot open or write to the file" even though I am quite sure that the accounts the SQL Server service and SQL Server Agent are running under have full access to the file.
Also, if I execute the stored procedure as part of an SSIS package it works, whether I run the package manually or as part of a scheduled job. This is actually how I'm doing it for now.
The issue only occurs if it's an XLS formatted file; XLXS work as expected.