October 28, 2013 at 5:46 am
Hi Guys,
I hope you can help, i have the following script setup as part of an integration package to archive the order file for a customer on an FTP once it is processed. However, the script has been written so even if it doesn't succeed the package continues and does not fail- if the script does not run successfully i need it to fail so it can be dealt with manually as currently it is causing order files to duplicate as they are not archived
Hope you can help
Thanks
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
Imports System
Imports System.Collections
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Try
If Dts.Variables("User::FTPIPAddress").Value Is Nothing Then
Dts.TaskResult = ScriptResults.Success
Return
End If
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the connection manager properties
cm.Properties("ServerName").SetValue(cm, Convert.ToString(Dts.Variables("User::FTPIPAddress").Value))
cm.Properties("ServerUserName").SetValue(cm, Convert.ToString(Dts.Variables("User::FTPUser").Value))
cm.Properties("ServerPassword").SetValue(cm, Convert.ToString(Dts.Variables("User::FTPPassword").Value))
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0")
cm.Properties("ChunkSize").SetValue(cm, "1000")
cm.Properties("Retries").SetValue(cm, "5")
cm.Properties("UsePassiveMode").SetValue(cm, True)
'create the FTP object that receives the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connect to the ftp server
'Set the directory to be accessed on the server
http://ftp.SetWorkingDirectory(Convert.ToString(Dts.Variables("User::FTPMainFolder").Value))
'Create arrays to hold the folders and file names of the files to be copied to the
'localfolder. The FolderNames aren't used, but is required for the next method to
'work.
Dim FileNames() As String = Nothing
Dim FolderNames() As String = Nothing
'Populate the arrays
http://ftp.GetListing(FolderNames, FileNames)
'Exit if there are no files to process
If (FileNames Is Nothing) Then
Dts.Variables("User::NoFileToBeProcessed").Value = True
Return
End If
'Set the boolean flag to false
Dts.Variables("User::NoFileToBeProcessed").Value = False
'Copy the files to the local folder
http://ftp.ReceiveFiles(FileNames, Dts.Variables("User::ILGClientFolder").Value, True, False)
Dim strFileSize As String = Nothing
Dim di As New IO.DirectoryInfo(Dts.Variables("User::ILGClientFolder").Value)
Dim aryFi As IO.FileInfo() = di.GetFiles
'Dim fi As IO.FileInfo
Dim FileList(0 To aryFi.Length - 1) As String
For I = 0 To aryFi.Length - 1
FileList(I) = aryFi(I).FullName
Next
'Copy the files to the FTP folder
http://ftp.SendFiles(FileList, Dts.Variables("User::FTPArchiveFolder").Value, True, False)
'Delete the copied files from the ftp server
http://ftp.DeleteFiles(FileNames)
'Close the connection
Catch ex As Exception
' Fire error and set result to failure
Dts.Events.FireError(0, "FTP file receiving ", "Error: " + ex.Message, String.Empty, 0)
Dts.TaskResult = ScriptResults.Failure
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
October 28, 2013 at 7:40 am
The very last line in your subroutine sets the result to success regardless of what happened above.
"Dts.TaskResult = ScriptResults.Success"
If you move that line to the top of your subroutine, that should fix your problem.
October 28, 2013 at 8:19 am
Many thanks for responding- i have made the changes so it now looks like this but the integration package did not fail when the file tried to archive? Have I done something wrong?
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
Imports System
Imports System.Collections
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dts.TaskResult = ScriptResults.Success
Try
If Dts.Variables("User::FTPIPAddress").Value Is Nothing Then
Dts.TaskResult = ScriptResults.Success
Return
End If
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the connection manager properties
cm.Properties("ServerName").SetValue(cm, Convert.ToString(Dts.Variables("User::FTPIPAddress").Value))
cm.Properties("ServerUserName").SetValue(cm, Convert.ToString(Dts.Variables("User::FTPUser").Value))
cm.Properties("ServerPassword").SetValue(cm, Convert.ToString(Dts.Variables("User::FTPPassword").Value))
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0")
cm.Properties("ChunkSize").SetValue(cm, "1000")
cm.Properties("Retries").SetValue(cm, "5")
cm.Properties("UsePassiveMode").SetValue(cm, True)
'create the FTP object that receives the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connect to the ftp server
'Set the directory to be accessed on the server
http://ftp.SetWorkingDirectory(Convert.ToString(Dts.Variables("User::FTPMainFolder").Value))
'Create arrays to hold the folders and file names of the files to be copied to the
'localfolder. The FolderNames aren't used, but is required for the next method to
'work.
Dim FileNames() As String = Nothing
Dim FolderNames() As String = Nothing
'Populate the arrays
http://ftp.GetListing(FolderNames, FileNames)
'Exit if there are no files to process
If (FileNames Is Nothing) Then
Dts.Variables("User::NoFileToBeProcessed").Value = True
Return
End If
'Set the boolean flag to false
Dts.Variables("User::NoFileToBeProcessed").Value = False
'Copy the files to the local folder
http://ftp.ReceiveFiles(FileNames, Dts.Variables("User::ILGClientFolder").Value, True, False)
Dim strFileSize As String = Nothing
Dim di As New IO.DirectoryInfo(Dts.Variables("User::ILGClientFolder").Value)
Dim aryFi As IO.FileInfo() = di.GetFiles
'Dim fi As IO.FileInfo
Dim FileList(0 To aryFi.Length - 1) As String
For I = 0 To aryFi.Length - 1
FileList(I) = aryFi(I).FullName
Next
'Copy the files to the FTP folder
http://ftp.SendFiles(FileList, Dts.Variables("User::FTPArchiveFolder").Value, True, False)
'Delete the copied files from the ftp server
http://ftp.DeleteFiles(FileNames)
'Close the connection
Catch ex As Exception
' Fire error and set result to failure
Dts.Events.FireError(0, "FTP file receiving ", "Error: " + ex.Message, String.Empty, 0)
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub
End Class
October 28, 2013 at 9:51 am
Hmm, sorry I don't know then.
Can you confirm that the catch block is firing?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply