FTPS Task

Library references

• System
• System.Data
• Microsoft.SqlServer.ScriptTask
• Microsoft.SqlServer.ManagedDTS
• CozyRoc.SSISPlus
• System.Design
• System.Drawing.Design
• System.Drawing

ScriptMain

' Microsoft SQL Server Integration Services Script Task ' ' Copyright (c) 2007 CozyRoc LLC ' ' Permission is hereby granted, free of charge, to any person ' obtaining a copy of this software and associated documentation ' files (the "Software"), to deal in the Software without ' restriction, including without limitation the rights to use, ' copy, modify, merge, publish, distribute, sublicense, and/or sell ' copies of the Software, and to permit persons to whom the ' Software is furnished to do so, subject to the following ' conditions: ' ' The above copyright notice and this permission notice shall be ' included in all copies or substantial portions of the Software. ' ' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, ' EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES ' OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ' NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT ' HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, ' WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING ' FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR ' OTHER DEALINGS IN THE SOFTWARE. Imports System Imports System.IO Imports System.Text Imports System.Collections Imports System.ComponentModel Imports System.Windows.Forms.Design Imports System.Drawing.Design Imports System.Net Imports System.Security.Cryptography.X509Certificates Imports Microsoft.SqlServer.Dts.Runtime Imports CozyRoc.SqlServer.SSIS.Attributes ' ' ScriptMain ' <SortProperties(New String() {"FtpUrl", "Username", "Password", "Action", "Local", "Remote", "RenameTo"})> _ <FilterProperties("GetProperties")> _ <ValidateProperties("Validate")> _ Public Class ScriptMain ' ' Consts ' Private Class Consts Public Shared ActionUpload As String = "Upload file" Public Shared ActionDownload As String = "Download file" Public Shared ActionMkDir As String = "Create remote directory" Public Shared ActionRmDir As String = "Remove remote directory" Public Shared ActionDelete As String = "Remove remote file" Public Shared ActionRename As String = "Rename remote file" Public Shared ActionTypes As Object() = New Object() { _ ActionUpload, _ ActionDownload, _ ActionMkDir, _ ActionRmDir, _ ActionDelete, _ ActionRename} End Class ' Consts ' ' TrustAllCertificatePolicy ' Private Class TrustAllCertificatePolicy Implements ICertificatePolicy Public Sub TrustAllCertificatePolicy() End Sub ' TrustAllCertificatePolicy Public Function CheckValidationResult( _ ByVal srvPoint As ServicePoint, _ ByVal certificate As X509Certificate, _ ByVal request As WebRequest, _ ByVal certificateProblem As Integer) As Boolean Implements ICertificatePolicy.CheckValidationResult CheckValidationResult = True End Function ' CheckValidationResult End Class ' TrustAllCertificatePolicy Public Sub Main() Dim result As Integer Dim request As FtpWebRequest Dim response As FtpWebResponse Dim input As Stream Dim output As Stream Dim remote As String Dim fireAgain As Boolean result = Dts.Results.Success Try ' Setup to trust all server certificates. ServicePointManager.CertificatePolicy = New TrustAllCertificatePolicy() ' Setup action. Select Case Me.Action Case Consts.ActionUpload ' Setup FTPS request. request = CType(WebRequest.Create(GetRemote_()), FtpWebRequest) Call SetupRequest_(request) request.Method = WebRequestMethods.Ftp.UploadFile input = New FileStream(Me.Local, FileMode.Open, FileAccess.Read) output = request.GetRequestStream() ' Upload file. Try Call TransferFile_(input, output) Finally input.Close() output.Close() End Try response = CType(request.GetResponse(), FtpWebResponse) Case Consts.ActionDownload ' Setup FTPS request. request = CType(WebRequest.Create(GetRemote_()), FtpWebRequest) Call SetupRequest_(request) request.Method = WebRequestMethods.Ftp.DownloadFile ' Get input. response = CType(request.GetResponse(), FtpWebResponse) input = response.GetResponseStream() ' Get output. If File.Exists(Me.Local) Then ' File exists. Resume download. request.ContentOffset = New FileInfo(Me.Local).Length output = New FileStream(Me.Local, FileMode.Append, FileAccess.Write) Else output = New FileStream(Me.Local, FileMode.Create, FileAccess.Write) End If ' Download file. Try Call TransferFile_(input, output) Finally input.Close() output.Close() End Try Case Consts.ActionMkDir ' Setup FTPS request. request = CType(WebRequest.Create(GetRemote_()), FtpWebRequest) Call SetupRequest_(request) request.Method = WebRequestMethods.Ftp.MakeDirectory response = CType(request.GetResponse(), FtpWebResponse) Case Consts.ActionRmDir ' Setup FTPS request. request = CType(WebRequest.Create(GetRemote_()), FtpWebRequest) Call SetupRequest_(request) request.Method = WebRequestMethods.Ftp.RemoveDirectory response = CType(request.GetResponse(), FtpWebResponse) Case Consts.ActionDelete ' Setup FTPS request. request = CType(WebRequest.Create(GetRemote_()), FtpWebRequest) Call SetupRequest_(request) request.Method = WebRequestMethods.Ftp.DeleteFile response = CType(request.GetResponse(), FtpWebResponse) Case Consts.ActionRename ' Setup FTPS request. request = CType(WebRequest.Create(GetRemote_()), FtpWebRequest) Call SetupRequest_(request) request.Method = WebRequestMethods.Ftp.MakeDirectory request.RenameTo = Me.RenameTo response = CType(request.GetResponse(), FtpWebResponse) Case Else Throw New ArgumentException("Unhandled action type.") End Select ' Report response status description. Call Dts.Events.FireInformation( _ 0, _ String.Empty, _ response.StatusDescription, _ String.Empty, _ 0, _ fireAgain) Catch ex As Exception result = Dts.Results.Failure Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0) Finally If Not response Is Nothing Then Call response.Close() End If End Try Dts.TaskResult = result End Sub ' Main #Region "Properties" <Category("Connection")> _ <Description("Specifies URL of FTPS server.")> _ Public Property FtpUrl() As String Get FtpUrl = m_ftpUrl End Get Set(ByVal value As String) m_ftpUrl = value End Set End Property ' FtpUrl <Category("Connection")> _ <Description("Specifies user name for connection to FTPS server.")> _ Public Property Username() As String Get Username = m_userName End Get Set(ByVal value As String) m_userName = value End Set End Property ' Username <Category("Connection")> _ <Description("Specifies password for connection to FTPS server.")> _ <PasswordPropertyText(True)> _ Public Property Password() As String Get Password = m_password End Get Set(ByVal value As String) m_password = value End Set End Property <Category("Settings")> _ <Description("Specifies the action that the task performs.")> _ <List("GetActionTypes")> _ <RefreshProperties(RefreshProperties.All)> _ Public Property Action() As String Get Action = m_action End Get Set(ByVal value As String) m_action = value End Set End Property ' Action <Category("Settings")> _ <Description("Specifies the local parameter.")> _ <Editor(GetType(FileNameEditor), GetType(UITypeEditor))> _ Public Property Local() As String Get Local = m_local End Get Set(ByVal value As String) m_local = value End Set End Property ' Local <Category("Settings")> _ <Description("Specifies the remote parameter.")> _ Public Property Remote() As String Get Remote = m_remote End Get Set(ByVal value As String) m_remote = value End Set End Property ' Remote <Category("Settings")> _ <Description("Specifies new name of remote.")> _ Public Property RenameTo() As String Get RenameTo = m_local End Get Set(ByVal value As String) m_local = value End Set End Property ' RenameTo #End Region ' Properties #Region "Internals" Public Function GetActionTypes() As Object() GetActionTypes = Consts.ActionTypes End Function ' GetActionTypes Public Function GetProperties() As String() Dim result As New ArrayList() ' Include base settings. Call result.Add("FtpUrl") Call result.Add("Username") Call result.Add("Password") Call result.Add("Action") ' Setup local and remote based on action type. Select Case Me.Action Case Consts.ActionUpload, Consts.ActionDownload Call result.Add("Local") Call result.Add("Remote") Case Consts.ActionMkDir, Consts.ActionRmDir, Consts.ActionDelete Call result.Add("Remote") Case Consts.ActionRename Call result.Add("Remote") Call result.Add("RenameTo") Case Else Throw New ArgumentException("Unhandled action type") End Select Return CType(result.ToArray(GetType(String)), String()) End Function ' GetProperties Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean result = False If String.IsNullOrEmpty(Me.FtpUrl) Then errMessage = "Please specify FTPS URL." ElseIf String.IsNullOrEmpty(Me.Username) Then errMessage = "Please specify user name." ElseIf String.IsNullOrEmpty(Me.Action) Then errMessage = "Please specify task action." ElseIf String.IsNullOrEmpty(Me.Local) And _ (Me.Action = Consts.ActionUpload Or Me.Action = Consts.ActionDownload) Then errMessage = "Please specify local parameter." ElseIf String.IsNullOrEmpty(Me.Remote) Then errMessage = "Please specify remote parameter." Else result = True End If Validate = result End Function ' Validate Private Sub SetupRequest_(ByVal request As FtpWebRequest) request.EnableSsl = True request.UseBinary = True request.Credentials = New NetworkCredential(Me.Username, Me.Password) End Sub ' SetupRequest_ Private Sub TransferFile_(ByVal input As Stream, ByVal output As Stream) Dim buf(1024) As Byte Dim bytesRead As Integer Do bytesRead = input.Read(buf, 0, buf.Length) If bytesRead > 0 Then output.Write(buf, 0, bytesRead) End If Loop While bytesRead > 0 End Sub ' TransferFile_ Private Function GetRemote_() As String Dim result As String ' Setup remote directory to create. result = Me.FtpUrl If Right(result, 1) <> "/" Then result = result + "/" End If result = result + Me.Remote GetRemote_ = result End Function ' GetRemote_ #End Region ' Internals #Region "Attributes" Private m_ftpUrl As String Private m_userName As String Private m_password As String Private m_action As String = Consts.ActionUpload Private m_local As String Private m_remote As String #End Region ' Attributes End Class ' ScriptMain