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?
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
User contributions licensed under CC BY-SA 3.0