I have a number of SSIS jobs that open some Excel files and manipulate them. The task within the job that opens the Excel file is basically the same in each job, it just points to a different Excel file. Each of these jobs work fine except for one. But even this one works when ran manually from the package and not in the job.
The applicable code is:
Dim reportLocation As String
Dim oXL As Microsoft.Office.Interop.Excel.Application
Dim oWB As Microsoft.Office.Interop.Excel.Workbook
reportLocation = "\\testlocation\share\test.xlsm"
'Open Excel instance
oXL = New Microsoft.Office.Interop.Excel.Application
oXL.Visible = False
'Open the Excel file to edit
oWB = oXL.Workbooks.Open(reportLocation) 'Error here
The error received is as below:
Microsoft Excel cannot access the file '\\testlocation\share\test.xlsm'. There are several possible reasons:
• The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook. at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad) at ScriptTask_ffa2c543b1224c3987d5764694df1079.ScriptMain.Main()
This was the latest package to be created, and was a copy/paste job, just changing the locations it looks for the files. I have checked all permissions and there is no issue there. I've even given access to "Everyone" to see if that was an issue, but the same error occurred. The file definitely exists in the location and it is not open by anyone. What possible reason is there that this specific file can't be opened when run in a SQL job but can be opened when run manually in the package? I have also tried changing the job to run the package as a user with full admin access via a proxy. But still no joy.