SSIS Script Task Fails when run from SQL Agent

Question

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.

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
– William Salzman, Commented Apr 1, 2015 at 20:20
@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
– GPH, Commented Apr 2, 2015 at 6:28
If you log on to the server and run the package as yourself, does it work? — billinkc
– billinkc, Commented Apr 6, 2015 at 1:58
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
– billinkc, Commented Apr 6, 2015 at 2:02
@WilliamSalzman I could have saved some typing had I read your comment closer... — billinkc
– billinkc, Commented Apr 7, 2015 at 19:58

Thomas Nilsson · Accepted Answer · 2015-06-22 13:14:31Z

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

answered Jun 22, 2015 at 13:14

Thomas Nilsson

562 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GPH Over a year ago

Thank you so much for this. Absolutly crazy that a bug like this exists! Thanks so much!!!

Mike Honey · Accepted Answer · 2015-04-01 04:26:31Z

-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

answered Apr 1, 2015 at 4:26

Mike Honey

15.2k1 gold badge29 silver badges45 bronze badges

1 Comment

GPH Over a year ago

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:

Collectives™ on Stack Overflow

SSIS Script Task Fails when run from SQL Agent

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related