I'm with a big problem in an SSIS project.
When I execute the project in SSIS Local Machine the dtsx runs well, but when I put that in a SQL Agent Job it throws me an error:
Source: File In Folder? Description: Exception has been thrown by the target of an invocation. End Error Error: 2020-10-02 17:37:05.33 Code: 0x00000001 Source: Script Task 1 Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 17:37:01 Finished: 17:37:05 Elapsed: 3.687 seconds. The package execution failed. The step failed.
The source File In Folder is a script task, with this code:
Dim StrFolderArrary As String()
Dim StrFileArray As String()
Dim fileName As String
Dim RemoteDirectory As String
RemoteDirectory = Dts.Variables("User::ftp_masks").Value.ToString()
Dim cm As ConnectionManager = Dts.Connections("FTP Connection Manager") 'FTP connection manager name
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
Try
ftp.Connect() 'Connecting to FTP Server
ftp.SetWorkingDirectory(RemoteDirectory) 'Provide the Directory on which you are working on FTP Server
ftp.GetListing(StrFolderArrary, StrFileArray) 'Get all the files and Folders List
'If there is no file in the folder, strFile Arry will contain nothing, so close the connection.
If StrFileArray Is Nothing Then
MessageBox.Show(Dts.Variables("User::FileExistsFlg").Value.ToString())
Dts.Variables("User::FileExistsFlg").Value = 0
ftp.Close()
'If Files are there, Loop through the StrFileArray arrary and insert into table
Else
For Each fileName In StrFileArray
MessageBox.Show(fileName)
Dts.Variables("User::files").Value = fileName
MessageBox.Show(Dts.Variables("User::files").Value.ToString())
If fileName = Dts.Variables("User::files").Value.ToString() Then
Dts.Variables("User::FileExistsFlg").Value = 1
MessageBox.Show(Dts.Variables("User::FileExistsFlg").Value.ToString())
End If
Next
Dts.TaskResult = ScriptResults.Success
ftp.Close()
End If
MessageBox.Show("End try")
Catch ex As Exception
MessageBox.Show("Catch")
Dts.Events.FireError(0, "My File Task", ex.Message, String.Empty, 0)
MessageBox.Show("Stack Trace: " & vbCrLf & ex.StackTrace)
Throw New ApplicationException("Something happened :(", ex)
'Dts.TaskResult = ScriptResults.Failure
Finally
' This line executes whether or not the exception occurs.
MessageBox.Show("in Finally block")
End Try
'
' Add your code here
'
Dts.TaskResult = ScriptResults.Success
Can someone help me? How do I see the specific error?
Thank you for your time.
One of the challenges with your code is the use of MessageBox. That is an interactive UI element. You cannot use those when an SSIS package runs in unattended mode because there would be a popup on the server, blocking all progress until someone logged in and clicked "OK". Yes, that totally happened back in the DTS days (pre 2005).
A way to keep your popups is to test for the System scoped variable of InteractiveMode. You need to check it in the ReadOnly variables list and then modify your code as
VB approximate
' This is not valid VB syntax but I can't remember their declaration/initialization sytnax
Dim interactiveMode as Boolean = (bool) Dts.Variables["System::InteractiveMode"].Value
if interactiveMode then
MessageBox.Show(fileName)
End If
A better way than that is to raise Information messages. Those work in both attended an unattended mode.
This is C#, you're welcome to convert to VB syntax
bool fireAgain = false;
string message = "{0}:{1}";
Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, "RemoteDirectory", RemoteDirectory), string.Empty, 0, ref fireAgain);
Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, "fileName", fileName), string.Empty, 0, ref fireAgain);
That would dump to the information logs the remote directory and the fileName values. In VS, that will show in the Output window as well as the Package Execution Results graphical window. On the server, that will dump to SSISDB.catalog.OperationMessages
All of that said, what else could be going on?
User contributions licensed under CC BY-SA 3.0