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) 2007-2008 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.ComponentModel Imports System.Collections Imports Microsoft.SqlServer.Dts.Runtime Imports CozyRoc.SqlServer.SSIS.Attributes <SortProperties(New String() {"ProxyUrl", "Username", "Password", "Action", "RemoteUrl", "SourceFile", "TargetFile"})> _ <FilterProperties("GetProperties")> _ <ValidateProperties("Validate")> _ Public Class ScriptMain ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Public Sub Main() Dim result As Integer Dim client As WebClient Dim fireAgain As Boolean 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, 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) 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("Action") Call result.Add("RemoteUrl") Select Case Me.Action Case "Upload File" Call result.Add("SourceFile") 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("Specify 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("Specify 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("Specify 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("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("Specify 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 #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_ #End Region ' Internals #Region "Attributes" Private m_proxyUrl As String Private m_username As String Private m_password As String Private m_action As String Private m_remoteUrl As String Private m_fileName As String #End Region ' Attributes End Class ' ScriptMain