none
SSIS 2008 R2 - Script task fails when run as job

    Question

  • Hi 

    I have a script task which opens a csv file using excel and save it as Excel File. I chose this method after a long struggle to use it as flat file source (too many issues I had). It jsut become so much reliable to work with after converting it as Excel.

    The issue I have is when I run the script as a job, it fails. It works fine in BID. 

    1. I am using Proxy (admin) account to register it as a job. 

    2. in Execution options, 'Use 32 bit' runtime is ticked 

    I have other SSIS packages scheduled as jobs and they are all running fine (including 32 bit runtime). 

    It's just this script this causing me a lot of headaches. 

    The error message I got is 

    System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'G:\ReadingEggFTP\AU\20130619\readingeggs_3pau_school_marketing_customer_list_20130619.csv'. 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_a340a727a31942219e1e112705df885a.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()

    • The file name or path does not exist - Yes it does. Otherwise I couldn't do it in BID. 

    • The file is being used by another program - I am just running the script only (Testing by each object)

    • The workbook you are trying to save has the same name as a currently open workbook - It is the same name but the extension is differe

    nt). Again, it works in BIDS.


    The following is the script code (I got it from a web)


    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports Microsoft.Office.Interop.Excel

    Enum ScriptResults
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum


    Public Sub Main()
            Dim excel As New Microsoft.Office.Interop.Excel.Application
            Dim wb As Microsoft.Office.Interop.Excel.Workbook
            Dim FiletoOpen As String
            '  Dim ws As Microsoft.Office.Interop.Excel.Worksheet

            FiletoOpen = Dts.Variables("User::FTPAUFileDestinationPath").Value.ToString + "\" + Dts.Variables("User::FTPFileAUDownloaded").Value.ToString

            wb = excel.Workbooks.Open(FiletoOpen)
            ''  ws = wb.Worksheets(1)

            wb.SaveAs((Dts.Variables("User::AUUploadFile").Value.ToString), FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8)

            wb.Close()
            excel.Quit()

            Runtime.InteropServices.Marshal.ReleaseComObject(excel)

            Dts.TaskResult = ScriptResults.Success
    End Sub

    End Class


    Thanks!

    • Edited by SQLMa Wednesday, June 19, 2013 6:56 AM
    Wednesday, June 19, 2013 5:09 AM

All replies

  • Where is the CSV and Excel file stored?

    Does the proxy used in job have read/write access to these locations?


    Let me know if you have any more questions on this. Please mark this post as an answer if it helps you resolve your question. - Bhawesh

    Wednesday, June 19, 2013 5:36 AM
  • Hi Bhawesh 

    Thanks for the reply!!

    Yes. the proxy is the admin account and can read/write to the file which is stored in the local storage. 

    Cheers

    Thursday, June 20, 2013 1:05 AM
  • Open Task Manager and go to "Processes" tab. Click the button which says "Show Process from all users". And check if there are any 'Orphaned' (which were opened/created earlier by script task, but did not end for some reason) open excel processes.

    If so, kill those processes. I would suggest do not keep any excel instance open while the job is running.

    Let me know if this helps.


    Let me know if you have any more questions on this. Please mark this post as an answer if it helps you resolve your question. - Bhawesh

    Thursday, June 20, 2013 5:47 AM
  • thank you for the reply, Bhawesh. 

    Are you asking to check if 'Excel.exe' is running in processes tab? 

    I already checked it and it is not there. 

    As I said, I always can do it in BIDs. Every morning, I would get a reminder email saying that the job failed. I open it in SSIS and simply run it. Then I can see it starts from the script where it failed and successfully go through all the steps. (check point enabled). 

    Cheers

    Friday, June 21, 2013 12:56 AM
  • login to windows using the credentials you used in proxy. Then try to run the package using BIDS. See if it runs or starts having problem.

    If it runs: there's a problem with the job configuration

    If it starts having problem: There's a problem with the proxy account's access to your files.

    hope this helps.


    Let me know if you have any more questions on this. Please mark this post as an answer if it helps you resolve your question. - Bhawesh

    Friday, June 21, 2013 11:09 AM
  • Hi Thanks for the reply.

    Well, as I said, they are all admin account. I have something similar packages and they all work fine except for this. 

    Friday, June 28, 2013 2:38 AM
  • Hi Thanks for the reply.

    Well, as I said, they are all admin account. I have something similar packages and they all work fine except for this. 

    Hi, does anyone could solve this truble? I have de same scenario and I can't deal with this anymore. I'm trying to save a HTML file (within a table) as Excel File with the same results.

    Wednesday, April 09, 2014 9:44 PM
  • Hello,

    In the above error message the file is stored on the G: Drive. If this is a network drive try using the UNC path name like \\server\folder\... instead of G:\...


    Jan D'Hondt - SQL server BI development

    Thursday, April 10, 2014 12:36 PM