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?

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

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?

When you say this "works from BIDS" - where is BIDS running? On the server itself, or on your machine? Talk to me now on

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.

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

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

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?

Sorry Jeff, I missed your post there. Yes, 'Execution Options>>Use 32 bit runtime' is checked in the package job step.

Have you tried with it turned off :-)Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

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.

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

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.

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

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

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!

Glad you got it working :-)Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

Thanks Jeff.

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..

How did you add and configured Microsoft Excel Application? Thank you!

Hello Mr. Wharty, I have the same problem. Here is the info below: My server is Windows 2008 Server R2 Data Center edition, Excel 2007 and SQL Server 2008 R2 Enterprise edition. I have a SSIS package that runs successfully in business intelligence development studio but fails to run under sql server agent as a job. The SSIS package processes an excel file from a file location. I have tried the solutions above. Still does not work Do you know what else could be the problem. Here is my error below: Executed as user: SCONET\svc_BIGCCPRD84_SSA. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 3:31:05 PM Error: 2012-04-18 15:31:10.07 Code: 0xC002F304 Source: Email error Send Mail Task Description: An error occurred with the following error message: "Failure sending mail. System.Net.WebException: Unable to connect to the remote server System.Net.Sockets.SocketException: An attempt was made to access a socket in a way forbidden by its access permissions 10.1.1.243:25". End Error Error: 2012-04-18 15:31:10.07 Code: 0x00000001 Source: Unlock workbook 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 '\\SCONET\Data\SCO\Group\LGCR Files For Upload\2010-Group 2-12264807800-4112012.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.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 ST_22080cd5ccf5484fa05df02546b8d5de.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: 3:31:05 PM Finished: 3:31:10 PM Elapsed: 4.672 seconds. The package execution failed. The step failed.