1

I’ve delete and rewritten this question as I don’t think I was being clear and it caused some frustration.

I have a SSIS package that creates and populates an Excel sheet, the package runs perfectly and creates the files as desired and can be executed on a schedule from SQL Server Agent without any issues.

The issue comes in when I try and execute a script task which executes some VB script to delete a specific row in the excel file. 

Public Sub Main()
    '


    Dim xlApp As Excel.Application
    Dim xlSheet As Excel.Worksheet

    xlApp = New Excel.Application
    xlApp.Workbooks.Open(Dts.Variables("NewFileName").Value.ToString)
    xlSheet = xlApp.Workbooks(1).ActiveSheet


    xlSheet.Rows(4).Delete()

    xlApp.Workbooks(1).Save()
    xlApp.Workbooks(1).Close()
    xlSheet = Nothing

    '
    Dts.TaskResult = ScriptResults.Success
End Sub

Now this runs perfectly inside the BIDS environment and does exactly what I need. However once the package is deployed the job fails giving a error

Source: Delete Header Row Description: 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:\Folder\Folder1\Status File\Status26032015.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.

The package is executed as ServerName\Administrator which does have access to the G: (as it would fail when it creates the file since this is done with the same variable). All the articles I have checked point to the SQL Server Agent Permissions however as 90% of the job runs through, which includes creating a file in the G drive surely it must have access.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
GPH
  • 1,817
  • 3
  • 29
  • 50
  • Try doing a more thorough memory clean up. After running your code a few times, you may have multiple copies of Excel.exe running on your machine. For example, look at the clean up area of this solutiion: http://sqlage.blogspot.com/2013/12/ssis-how-to-get-most-recent-file-from.html – Anoop Verma Mar 28 '15 at 23:47
  • 1
    Since you are creating the excel file in that SSIS package, is it possible that the connection has not been destroyed when you are then moving to the next step and trying to delete the row? i.e. the task that creates the excel file and populates it still retains a connection by the time the second task starts and attempts to connect to the new excel file. Can you break the process into two distinct packages so that the clean up of the connections happens before the row drop connection is established? – William Salzman Apr 01 '15 at 20:20
  • 1
    @william-salzman Have Allready tried seperating the script task into a seperate package, even created a batch script that killed any excel.exe that may be running but still get the error – GPH Apr 02 '15 at 06:28
  • 1
    If you log on to the server and run the package as yourself, does it work? – billinkc Apr 06 '15 at 01:58
  • 1
    Also, if create a second package. Heck, you can copy paste the existing package but disable everything except your delete script. In the original, disable the delete script. The original should run just fine from agent as you've stated. Then run the delete script package via agent, does it work? – billinkc Apr 06 '15 at 02:02
  • @billinkc you read my mind... – William Salzman Apr 07 '15 at 19:57
  • 1
    @WilliamSalzman I could have saved some typing had I read your comment closer... – billinkc Apr 07 '15 at 19:58

2 Answers2

4

Try to add this folder on the server running the SQL Agent Job; C:\Windows\SysWOW64\config\systemprofile\Desktop

It solved the similar issue I was facing.

More info here: https://social.msdn.microsoft.com/forums/sqlserver/en-US/34e5596a-4d02-4499-8a4e-8dad9fa27528/ssis-2008-r2-script-task-fails-when-run-as-job

Thomas Nilsson
  • 56
  • 2
  • Thank you so much for this. Absolutly crazy that a bug like this exists! Thanks so much!!! – GPH Jun 23 '15 at 14:36
-1

The SQL Server Agent process will not be able to translate any mapped drives e.g. G:\.

I believe this is because it does not start a complete windows session and does not load the user's profile.

I would convert the drive letter references to URI format e.g. \\myserver\fileshare

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • Hi Mike, its not a mapped drive, thats the drive that is on the server that the SSIS package runs on. The package runs fine up to the script task component which includes creating a new file in the G: – GPH Apr 02 '15 at 06:26