February 4, 2013 at 8:49 am
I am tasked with processing an excel file and the file is password protected. To do this, I have a Script task to save the file with out a password.
When I execute the package in a cmd window with DTExec (using 32bit version) it runs successfully. When i execute it in a SQL Agent job (with 32 bit execution) it fails.
The other interesting thing is, because I load many files from clients and they deliver them at different times, I have written a vb.net application to scan directories and execute the appropriate dtsx package. It executes using the .net "SHELL" command to run DTExec application. This app generates the same error as the SQL Agent job.
Code to re-save excel file with no password:
Public Sub Main()
Dim filetest As String = Dts.Variables("FileLocation").Value.ToString & Dts.Variables("FileName").Value.ToString
Dim excel As Excel.Application = New Excel.Application
Dim w As Workbook = excel.Workbooks.Open(filetest, 0, 1, 5, Dts.Variables("ExcelPassword").Value.ToString, , True)
w.SaveAs(Replace(filetest, ".xls", "_Clear.xls"), 39, "")
w.Close(False)
excel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel)
w = Nothing
excel = Nothing
Dim proc As System.Diagnostics.Process
For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
If proc.StartTime >= DateAdd(DateInterval.Second, -40, DateTime.Now) Then
proc.Kill()
End If
Next
Dts.TaskResult = ScriptResults.Success
End Sub
Error Message received:
ErrorMessage: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file '\\<FilePath>\Filename.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_c46544f3d5a1434ba60f8b1be96edba7.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()
I also tested and confirmed it is not a permissions problem.
February 4, 2013 at 11:09 am
I found the solution at:
http://stackoverflow.com/questions/4408336/system-runtime-interopservices-comexception-0x800a03ec
From site:
"The solution is to plug the difference between the way Windows 2003 and 2008 maintains its folder structure, because Office Interop depends on the desktop folder for file open/save intermediately. The 2003 system houses the desktop folder under systemprofile which is absent in 2008.
So when we create this folder on 2008 under the respective hierarchy as indicated below; the office Interop is able to save the file as required. This Desktop folder is required to be created under
C:\Windows\System32\config\systemprofile
AND
C:\Windows\SysWOW64\config\systemprofile"
What is stupid is I am running Interop 2010 on a Server 2008 box and this change still fixed my problem. What the heck!
February 5, 2013 at 5:36 am
ngreene (2/4/2013)
What is stupid is I am running Interop 2010 on a Server 2008 box and this change still fixed my problem. What the heck!
The issue is that Office was not designed to be used the way you're using it. Microsoft officially recommends against the use case you're implementing, an unattended process running code that uses the Excel object, so any issues you encounter are essentially yours alone to deal with:
http://support.microsoft.com/kb/257757
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 5, 2013 at 10:21 am
opc.three (2/5/2013)
ngreene (2/4/2013)
What is stupid is I am running Interop 2010 on a Server 2008 box and this change still fixed my problem. What the heck!The issue is that Office was not designed to be used the way you're using it. Microsoft officially recommends against the use case you're implementing, an unattended process running code that uses the Excel object, so any issues you encounter are essentially yours alone to deal with:
Thanks for the link, not seen that before.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply