none
SSIS Fail when running as SQL agent

    Question

  • Hi

    I have an SSIS package which download flat files and open and save one of them as Excel file. Then it extracts records from Excel to the database. Everything is done in one ssis package.

    The reason why I chose to save the file as Excel format is because I was not able to get data reliably  from the flat files.. After I did a lot of research and testing, I chose Excel as a way to do it.

    I stored SSIS package as File System. Whenever I run the package manually, it works fine. But when I schedule to run it via SQL Job agent, it always fails. and the failing part is a script which opens the downloaded flat file and save it as Excel file.

    I even set up  an admin proxy to do this but no use.

    I finally managed to get the error message (see below). I wonder if the error is due to 'The file is being used by another program'. Like I said, it works ok if I run manually. The file is definitely there...

    I am not sure where to go from here. Any suggestions?  What to test to confirm what...  or...   I don't know ..

    ah... any guide where I can go from here??

    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:\xxx\xxx\xxxxx\xxxxxxxxxxxxxxxx.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.



    • Edited by SQLMa Saturday, October 26, 2013 12:06 PM
    Saturday, October 26, 2013 12:04 PM

All replies

  • Hi

    Please check the following.

    1. The admin proxy account that you have access to the path to read & write.

    2. Make sure that excel is not open.

    3. Try implementing a logic to either drop the excel and recreate one, or replace the existing one, or create versions of excel.

    Cheers

    Mani@YourSQLMan.com


    Dr.Subramani Paramasivam

    Saturday, October 26, 2013 12:09 PM
  • Have you tried running the jobstep in 32bit?
    32bit office provider


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Saturday, October 26, 2013 12:21 PM
  • I have already done it.. 

    Thanks for the reply!!!

    Monday, October 28, 2013 12:03 AM

  • Thank you for the reply.. 

    1. admin proxy account is the local administrator account. FTP files are downloaded on the server itself therefore this should not be an issue. 

    2. No it's not

    3. Please see the current script below. Any improvement I can make?  (I only use C# for VSTO develpment not VB. I got an sample code from online and didn't bother to convert to C#) 

    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

            
    Monday, October 28, 2013 12:13 AM