Reputation: 1897
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.
Upvotes: 2
Views: 4931
Reputation: 56
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
Upvotes: 4
Reputation: 15017
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
Upvotes: -1