DTExec succesfull in CMD window, fails in Agent Job

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » DTExec succesfull in CMD window, fails in...

DTExec succesfull in CMD window, fails in Agent Job

Rate Topic

Display Mode

Topic Options

Author

Message

ngreene

Posted Monday, February 04, 2013 8:49 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 8:35 AM
Points: 18, Visits: 265

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.

Post #1415352

ngreene

Posted Monday, February 04, 2013 11:09 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 8:35 AM
Points: 18, Visits: 265

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!

Post #1415414

opc.three

Posted Tuesday, February 05, 2013 5:36 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1415766

Phil Parkin

Posted Tuesday, February 05, 2013 10:21 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 4,322, Visits: 9,661

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!

Thanks for the link, not seen that before.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1415983

« Prev Topic | Next Topic »

Permissions