SSIS Fails when run in job SQL Server

Question

I have a SSIS package that runs smoothly when I execute it from Visual Studio, but when I run it via SQL Server Job Task, it always fails to run and and throws this error:

Message Executed as user: hidden\hidden. Microsoft (R) SQL Server Execute Package Utility Version 11.0.6020.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 10:23:40 AM Error: 2018-12-17 10:23:40.63 Code: 0x00000001 Source: Update Report Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:23:40 AM Finished: 10:23:40 AM Elapsed: 0.594 seconds. The package execution failed. The step failed.

I don't know the exact problem, the other jobs work normally, I guess it could be related to the fact that we just migrated to a new server, and the other jobs are old jobs that are mirrored from the old server.

This SSIS package contains scripted tasks which modify an excel file. This is normal in my company because we run many automation tasks, all of which have been working normally until now.

Any idea to solve this problem?

Does the user executing the package have enough rights to access(or modify) the excel file? When encountering issues stating 'it runs fine in VS but when SQL agent is used it fails', Most of the times, it has to do something with rights and accounts executing the packages. The detailed error log should give a better insight in the actual problem. Consider including it in your question. — ppijnenburg

maztaxxx maztaxxx · Accepted Answer · 2018-12-18T04:48:07

finally i found the answer,

i have this script in my script task

Declare Function EndTask Lib "user32.dll" (ByVal hWnd As IntPtr) As Integer
Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" _
       (ByVal lpClassName As String, ByVal lpWindowName As String) As IntPtr
Declare Function GetWindowThreadProcessId Lib "user32.dll" _
       (ByVal hWnd As IntPtr, ByRef lpdwProcessId As Integer) As Integer
Declare Function SetLastError Lib "kernel32.dll" (ByVal dwErrCode As Integer) As IntPtr
Public Sub EnsureProcessKilled(ByVal MainWindowHandle As IntPtr, ByVal Caption As String)
    SetLastError(0)
    ' for Excel versions <10, this won't be set yet
    If IntPtr.Equals(MainWindowHandle, IntPtr.Zero) Then
        MainWindowHandle = FindWindow(Nothing, Caption)
    End If
    If IntPtr.Equals(MainWindowHandle, IntPtr.Zero) Then
        Exit Sub ' at this point, presume the window has been closed.
    End If
    Dim iRes, iProcID As Integer
    iRes = GetWindowThreadProcessId(MainWindowHandle, iProcID)
    If iProcID = 0 Then ' can’t get Process ID
        If EndTask(MainWindowHandle) <> 0 Then Exit Sub ' success
        Throw New ApplicationException("Failed to close.")
    End If
    Dim proc As System.Diagnostics.Process
    proc = System.Diagnostics.Process.GetProcessById(iProcID)
    proc.CloseMainWindow()
    proc.Refresh()
    If proc.HasExited Then Exit Sub
    proc.Kill()
End Sub

in this new server i dont have the rights to run this script to kill process, thats why the job always throw failed message for 0x00000001 error,

Thanks guys for trying to answer this problem

SSIS Fails when run in job SQL Server

1 Answers