Library references

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

ScriptMain

Option Explicit On ' Microsoft SQL Server Integration Services Script Task ' ' Copyright (c) 2006-2010 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.Net Imports System.Net.Security Imports System.ComponentModel Imports System.Collections Imports Microsoft.SqlServer.Dts.Runtime Imports CozyRoc.SqlServer.SSIS.Attributes <SortProperties(New String() { _ "ProxyUrl", "Username", "Password", "IgnoreServerCertificate", _ "Action", "RemoteUrl", "SourceFile", "TargetFile", "Method"})> _ <FilterProperties("GetProperties")> _ <ValidateProperties("Validate")> _ Public Class ScriptMain ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub Main() Dim result As Integer Dim client As WebClient Dim fireAgain As Boolean If Me.IgnoreServerCertificate Then ' Disable server certificate validation. ServicePointManager.ServerCertificateValidationCallback = DirectCast( _ System.Delegate.Combine( _ ServicePointManager.ServerCertificateValidationCallback, _ New RemoteCertificateValidationCallback(AddressOf BypassCertificateVerification_)), _ RemoteCertificateValidationCallback) End If Try ' Setup client connection. client = New WebClient() If Not String.IsNullOrEmpty(Me.ProxyUrl) Then ' Include proxy. client.Proxy = New WebProxy(Me.ProxyUrl) End If If Not String.IsNullOrEmpty(Me.Username) Then ' Include username/password credentials. client.Credentials = New NetworkCredential(Me.Username, Me.Password) End If ' Execute the action. Select Case Me.Action Case "Upload File" Call Dts.Events.FireInformation( _ 0, _ String.Empty, _ String.Format("Uploading '{0}' to '{1}'", Me.SourceFile, Me.RemoteUrl), _ String.Empty, _ 0, _ fireAgain) Call client.UploadFile(Me.RemoteUrl, Me.Method, GetConnectionFile_(Me.SourceFile)) Case "Download File" Call Dts.Events.FireInformation( _ 0, _ String.Empty, _ String.Format("Downloading '{0}' from '{1}'", Me.TargetFile, Me.RemoteUrl), _ String.Empty, _ 0, _ fireAgain) Call client.DownloadFile(Me.RemoteUrl, GetConnectionFile_(Me.TargetFile)) Case Else Throw New Exception("Unhandled task action.") End Select result = Dts.Results.Success Catch ex As Exception result = Dts.Results.Failure Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0) Finally If Me.IgnoreServerCertificate Then ' Enable server certificate validation. ServicePointManager.ServerCertificateValidationCallback = DirectCast( _ System.Delegate.Remove( _ ServicePointManager.ServerCertificateValidationCallback, _ New RemoteCertificateValidationCallback(AddressOf BypassCertificateVerification_)), _ RemoteCertificateValidationCallback) End If End Try Dts.TaskResult = result End Sub ' Main ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of current properties based on current state. Public Function GetProperties() As String() Dim result As New ArrayList ' Base properties. Call result.Add("ProxyUrl") Call result.Add("Username") Call result.Add("Password") Call result.Add("IgnoreServerCertificate") Call result.Add("Action") Call result.Add("RemoteUrl") Select Case Me.Action Case "Upload File" Call result.Add("SourceFile") Call result.Add("Method") Case "Download File" Call result.Add("TargetFile") 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 Dim props As Object() Try If String.IsNullOrEmpty(Me.RemoteUrl) Then Throw New ApplicationException("Please specify RemoteUrl.") End If If String.IsNullOrEmpty(m_fileName) Then Throw New ApplicationException("Please specify file name.") End If result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Connection")> _ <Description("Specifies proxy URL (optional).")> _ Public Property ProxyUrl() As String Get ProxyUrl = m_proxyUrl End Get Set(ByVal value As String) m_proxyUrl = value End Set End Property ' ProxyUrl ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Connection")> _ <Description("Specifies user name (optional).")> _ 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 (optional).")> _ <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 ' Password ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Connection")> _ <Description("Select to ignore server certificate.")> _ Public Property IgnoreServerCertificate() As Boolean Get IgnoreServerCertificate = m_ignoreServerCertificate End Get Set(ByVal value As Boolean) m_ignoreServerCertificate = value End Set End Property ' IgnoreServerCertificate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Settings")> _ <List(New Object() {"Upload File", "Download File"})> _ <Description("Select task action.")> _ <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 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' RemoteUrl property. <Category("Settings")> _ <Description("Specifies remote URL.")> _ Public Property RemoteUrl() As String Get RemoteUrl = m_remoteUrl End Get Set(ByVal value As String) m_remoteUrl = value End Set End Property ' RemoteUrl ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Settings")> _ <Connection("FileConnectionType")> _ <Description("Select connection to source file.")> _ Public Property SourceFile() As String Get SourceFile = m_fileName End Get Set(ByVal value As String) m_fileName = value End Set End Property ' SourceFile ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Settings")> _ <Connection("FileConnectionType")> _ <Description("Select connection to target file.")> _ Public Property TargetFile() As String Get TargetFile = m_fileName End Get Set(ByVal value As String) m_fileName = value End Set End Property ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Settings")> _ <Description("Specifies method used to send file (optional). If not specified default is POST.")> _ Public Property Method() As String Get Method = m_method End Get Set(ByVal value As String) m_method = value If m_method = "" Then m_method = Nothing End If End Set End Property ' Method #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private ReadOnly Property FileConnectionType() As String Get FileConnectionType = "FILE" End Get End Property ' FileConnectionType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Validate specified connection is the expected type. Private Function ValidateConnection_( _ ByVal managerName As String, _ ByVal expectedType As String) As Boolean Dim result As Boolean Dim manager As ConnectionManager If Not String.IsNullOrEmpty(managerName) Then manager = Dts.Connections(managerName) If manager.CreationName = expectedType Then result = True Else ' Doesn't match expected type. Dts.Events.FireError( _ 0, _ String.Empty, _ String.Format( _ "'{0}' connection is not '{1}' type.", _ managerName, _ expectedType), _ String.Empty, _ 0) End If End If ValidateConnection_ = result End Function ' ValidateConnection_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Retrieves file path from specified file connection manager. Private Function GetConnectionFile_(ByVal managerName As String) As String Dim result As String Dim manager As ConnectionManager Dim fileConnection As Object If ValidateConnection_(managerName, "FILE") Then manager = Dts.Connections(managerName) fileConnection = manager.AcquireConnection(Nothing) If Not fileConnection Is Nothing Then result = fileConnection.ToString() Else Dts.Events.FireError( _ 0, _ String.Empty, _ String.Format("''{0}' connection file doesn't exist.", managerName), _ String.Empty, _ 0) End If End If GetConnectionFile_ = result End Function ' GetConnectionFile_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Shared Function BypassCertificateVerification_( _ ByVal sender As Object, _ ByVal cert As System.Security.Cryptography.X509Certificates.X509Certificate, _ ByVal chain As System.Security.Cryptography.X509Certificates.X509Chain, _ ByVal errors As SslPolicyErrors) As Boolean BypassCertificateVerification_ = True End Function ' BypassCertificateVerification_ #End Region ' Internals #Region "Attributes" Private m_proxyUrl As String Private m_username As String Private m_password As String Private m_ignoreServerCertificate As Boolean Private m_action As String Private m_remoteUrl As String Private m_fileName As String Private m_method As String #End Region ' Attributes End Class ' ScriptMain