'cannot access the file' when run as an SQL Agent Job (works when executed from BIDS)
I have an SSIS Package, locally on the server. The user I am connected to the server as (CTRAK\sqladmin) is the same user that the SQL Agent is configured to use as the login. The xls file I am accessing is local to the server. I can successfully run the package in BIDS on the server. However, when I run it as an SQL Agent Job, I get an error that I've traced to the following line of code in the VB 2008 script: xlBook = xlApp.Workbooks.Open(vFilename) This is the error in the SQL Agent Job History: Executed as user: CTRAK\sqladmin. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 9:17:04 a.m. Error: 2011-05-18 09:17:04.58 Code: 0x00000001 Source: VB Script to Save each visit to the database then mark it as being loaded Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file 'C:\temp\VesselVisitStatistics.xls'. 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.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack) at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack) at ST_f17c023dfdb24b1584ceb7cefd1d8afe.vbproj.ScriptMain.LoadVesselVisitSpreadsheetToDatabase(String vFilename, Object xlApp, OleDbCommand& spSavePilotTimes) at ST_f17c023dfdb24b1584ceb7cefd1d8afe.vbproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:17:04 a.m. Finished: 9:17:04 a.m. Elapsed: 0.421 seconds. The package execution failed. The step failed. Any ideas why this runs successfully from BIDS when I am connected as CTRAK\sqladmin, but fails when run as an SQL Agent Job (again, connecting as CTRAK\sqladmin)? From the error message itself it seems clear that this is a permissions problem. However, I don't see how that can be, because: 1. CTRAK\sqladmin is a local administrator on the server (where the file resides) 2. I'm connected as CTRAK\sqladmin when the package runs successfully in BIDS 3. CTRAK\sqladmin is the login set up for SQL Agent jobs to run as Surely if it was a permissions problem then (2) would fail also? Related reading: http://bi-polar23.blogspot.com/2007/12/ssis-and-sql-server-agent.html (it seems permissions related, but I am using the same user to log in and to execute the SQL Agent job...) http://support.microsoft.com/kb/918760 (although for SQL Server 2005, not 2008 R2 like I am using) Any ideas?
May 17th, 2011 6:02pm

It appears that you have everything setup correctly so let's look at the other two issues that could be causing this error: ? The file is being used by another program. ? The workbook you are trying to save has the same name as a currently open workbook. You mentioned that your package works fine running from BIDS on the server. Is there any possibility that you have the 'C:\temp\VesselVisitStatistics.xls' file opened somewhere? Maybe in BIDS (i.e. as a source/destination connection) ? Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 6:24pm

Thanks for vindicating my sanity, although I guess it makes it harder to discover the real error :). I closed BIDS, confirmed that no Excel instance was running via Taskmanager, and reran the package, with the same error. It would appear that the error is something else. I also went and turned on text logging and reran it via both methods. These are the results of the logfiles from BIDS and SQL Agent: Logfile when run from BIDS (successfully) #Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message OnPreValidate,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{88F9BB84-9058-47D1-90BE-50308C1A8B1B},18/05/2011 10:29:50 a.m.,18/05/2011 10:29:50 a.m.,0,0x,(null) OnPreValidate,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{C1F47F80-8761-4846-90ED-2F68E5AA6902},18/05/2011 10:29:50 a.m.,18/05/2011 10:29:50 a.m.,0,0x,(null) OnPostValidate,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{C1F47F80-8761-4846-90ED-2F68E5AA6902},18/05/2011 10:29:51 a.m.,18/05/2011 10:29:51 a.m.,0,0x,(null) PackageStart,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{C1F47F80-8761-4846-90ED-2F68E5AA6902},18/05/2011 10:29:51 a.m.,18/05/2011 10:29:51 a.m.,0,0x,Beginning of package execution. Diagnostic,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{C1F47F80-8761-4846-90ED-2F68E5AA6902},18/05/2011 10:29:51 a.m.,18/05/2011 10:29:51 a.m.,0,0x,Based on the system configuration, the maximum concurrent executables are set to 3. OnPreExecute,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{C1F47F80-8761-4846-90ED-2F68E5AA6902},18/05/2011 10:29:51 a.m.,18/05/2011 10:29:51 a.m.,0,0x,(null) OnPostExecute,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{C1F47F80-8761-4846-90ED-2F68E5AA6902},18/05/2011 10:29:54 a.m.,18/05/2011 10:29:54 a.m.,0,0x,(null) PackageEnd,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{C1F47F80-8761-4846-90ED-2F68E5AA6902},18/05/2011 10:29:54 a.m.,18/05/2011 10:29:54 a.m.,0,0x,End of package execution. Logfile when run from SQL Agent (with error) #Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message OnPreValidate,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{AD2DDADC-FF34-417E-9DD7-AEA0F62E72B1},18/05/2011 10:30:33 a.m.,18/05/2011 10:30:33 a.m.,0,0x,(null) PackageStart,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{AD2DDADC-FF34-417E-9DD7-AEA0F62E72B1},18/05/2011 10:30:33 a.m.,18/05/2011 10:30:33 a.m.,0,0x,Beginning of package execution. Diagnostic,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{AD2DDADC-FF34-417E-9DD7-AEA0F62E72B1},18/05/2011 10:30:33 a.m.,18/05/2011 10:30:33 a.m.,0,0x,Based on the system configuration, the maximum concurrent executables are set to 3. OnPreExecute,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{AD2DDADC-FF34-417E-9DD7-AEA0F62E72B1},18/05/2011 10:30:33 a.m.,18/05/2011 10:30:33 a.m.,0,0x,(null) OnError,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{AD2DDADC-FF34-417E-9DD7-AEA0F62E72B1},18/05/2011 10:30:34 a.m.,18/05/2011 10:30:34 a.m.,1,0x,System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file 'C:\temp\VesselVisitStatistics.xls'. 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.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack) at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack) at ST_f17c023dfdb24b1584ceb7cefd1d8afe.vbproj.ScriptMain.LoadVesselVisitSpreadsheetToDatabase(String vFilename, Object xlApp, OleDbCommand& spSavePilotTimes) at ST_f17c023dfdb24b1584ceb7cefd1d8afe.vbproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() OnWarning,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{AD2DDADC-FF34-417E-9DD7-AEA0F62E72B1},18/05/2011 10:30:34 a.m.,18/05/2011 10:30:34 a.m.,-2147381246,0x,SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. OnPostExecute,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{AD2DDADC-FF34-417E-9DD7-AEA0F62E72B1},18/05/2011 10:30:34 a.m.,18/05/2011 10:30:34 a.m.,0,0x,(null) PackageEnd,POSRS,CTRAK\sqladmin,ProcessHarbourControlVesselStatsTest,{4AEA6B2C-6616-41A7-AB62-16DA16DF0429},{AD2DDADC-FF34-417E-9DD7-AEA0F62E72B1},18/05/2011 10:30:34 a.m.,18/05/2011 10:30:34 a.m.,1,0x,End of package execution. Unfortunately, not much more than what we already knew. It seems like the 'cannot access the file ' error has other less common causes, and I've hit one of them. Any ideas?
May 17th, 2011 6:40pm

When you say this "works from BIDS" - where is BIDS running? On the server itself, or on your machine? Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 7:12pm

Yes, I am running BIDS on the server itself (it also works on my PC, but that is neither here nor there). Some more details about the server configuration: This is a 64 bit server. It has the 64 bit version of SQL Server 2008 R2 installed. It has the 32 bit version of BIDS installed on it, as well as the 32 bit version of Excel 2007, and 32 bit Access Database Engine. I have successfully loaded/imported/scheduled several other jobs (some of whom also use VB 2008 scripts to launch Excel instances to manipulate spreadsheets). All of the jobs are using Run as='SQL Server Agent Service Account', and 'Execution options>>Use 32 bit runtime' is checked. But this file for some reason is proving intransigent.
May 17th, 2011 7:22pm

Is this the only package that is sourcing files from the location you've specified? And you have read this, yes? You may have more problems than you think you should... Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 7:46pm

Can you try setting the Use 32-Bit Runtime option to true (Found in the Execution Options tab of the Job Step) Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
May 17th, 2011 8:11pm

Thanks for the link. I had not read that particular page, although had got the gist of it from troubleshooting the excel and access OLEDB connections. I reconfigured some other excel-based packages to look at files in that folder and they ran successfully. Big picture, my problem is this: I have inherited a system where there are multiple Excel and Access 'databases' spawned accross the network. I am trying to consolidate them and import them into a SQL server database for some semblance of data integrity and to enable reporting that effectively spans multiple databases. What do you think is the best way of achieving this? It looks like I could potentially rewrite the 'Excel' SSIS packages to use Open XML instead of COM objects to access the spreadsheet, but what's the best way to import Access databases on a regular basis?
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 8:20pm

Sorry Jeff, I missed your post there. Yes, 'Execution Options>>Use 32 bit runtime' is checked in the package job step.
May 17th, 2011 11:56pm

Have you tried with it turned off :-)Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2011 12:49am

Heh. I have just done so and rerunning it gives the same error message. I've also tried upgrading the target spreadsheet from a *.xls 2003 spreadsheet to a *.xlsx 2007 version. Same error.
May 18th, 2011 1:01am

I think we're getting to the desperate stage now :-) With regards to: 1. CTRAK\sqladmin is a local administrator on the server (where the file resides) 2. I'm connected as CTRAK\sqladmin when the package runs successfully in BIDS 3. CTRAK\sqladmin is the login set up for SQL Agent jobs to run as When you run your package in BIDS, you may be connecting to SQL Server using CTRAK\sqladmin however the account you are logged onto the server as is the one that is used for file access permissions. Are you currently logged on to the server as CTRAK\sqladmin? If not, try using that account to run the package under. If yes, scratch headJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2011 1:09am

There are simpler ways of "moving" the data around from some source to another, if that is your requirement (unless I misinterpreted your previous comment). Instead of using a VB script to open and read data from an excel file (or more than one excel files), use a DataFlow task. Open it by double clicking on it, drag-drop an 'Excel Source" from the toolbox tab, double click on the Excel Source component and create a "New" connection to one of the Excel files by browsing to that location (just like you'd browse folders in Windows). That will setup your Excel source from which you want to extract the data. You can then have this data ported to , say a SQL Server database, by again dragging-dropping a OLEDB Destination component just after the Excel Source (again found in the toolbox) and connecting the two. You'd need to setup the ServerName/Database name of the destination SQL server database where you want to store this data (simply double click and follow the Wizard). You could try this approach for one Excel source, post the outcome here and we'll be able to guide you further on getting rest of the pieces in place. Hope this helps. Cheers!! Muqadder.
May 18th, 2011 3:42am

I can successfully run the package in BIDS on the server. However, when I run it as an SQL Agent Job, I get an error that I've traced to the following line of code in the VB 2008 script: xlBook = xlApp.Workbooks.Open(vFilename) Any ideas? Haha, this couldn't have happened at a better time!!! Just found this!! See if this is defines your problem. http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/01/22/the-ssis-and-excel-story-continues.aspx
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2011 4:05am

Have you tried using a File System Task to copy the file somewhere "exclusive", and then use that copy of the file? The FST only needs a read lock to copy the file, where the other actions need more permissions. That link found by Muqadder looks interesting... Talk to me now on
May 18th, 2011 7:28pm

That certainly does look like it hits all of the boxes for my problem. And.... It works! Thanks. I had to launch the 32 bit version of component services and manually add Microsoft Excel Application, but after that, I changed the identity to The Interactive User and bob's your uncle. Thanks!
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2011 9:52pm

Glad you got it working :-)Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
May 18th, 2011 9:53pm

Thanks Jeff.
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2011 10:02pm

1 little caveat when we are on x64 Windows 2008 (R2 also) Operating System where Component Services launched in 64 Bit does not show Microsoft Excel Application. In such a scenario we need to launch Component Services in x86 mode with the command MMC comexp.msc /32, rest of things remain the same. But you know what, I passed this stage of MS Excel access to SQLAgent Proxy account, but now get "Cannot create ActiveX Component".... looks like I have to search & grant access to the proxy account to create ActiveX Component by xp_Cmdshell ?.. Please let me know if any one is aware of it. Thanx in advance..
May 26th, 2011 12:10pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics