Library references

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

ScriptMain

' 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.ComponentModel Imports System.Collections Imports System.IO Imports System.Security.Cryptography Imports System.Text Imports Microsoft.SqlServer.Dts.Runtime Imports CozyRoc.SqlServer.SSIS.Attributes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <SortProperties(New String() { _ "IsFileVariable", "FileVariable", "File", _ "Algorithm", "ResultVariable"})> _ <FilterProperties("GetProperties")> _ <ValidateProperties("Validate")> _ Public Class ScriptMain ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub Main() Dim result As Integer Try ' Get input file. Dim file As String = GetFilePath_(Me.IsFileVariable, Me.File) ' Setup checksum provider. Dim checksumProvider As HashAlgorithm Select Case Me.Algorithm Case AlgorithmTypes.MD5 checksumProvider = New MD5CryptoServiceProvider() Case AlgorithmTypes.SHA1 checksumProvider = New SHA1CryptoServiceProvider() Case Else Throw New Exception("Unhandled checksum algorithm.") End Select ' Compute checksum. Dim checksumBytes() As Byte Using fileStream As Stream = System.IO.File.OpenRead(file) checksumBytes = checksumProvider.ComputeHash(fileStream) End Using ' Build checksum string. Dim sb As StringBuilder = New StringBuilder() For Each b As Byte In checksumBytes Call sb.Append(String.Format("{0:X2}", b)) Next ' Store checksum. Call StoreResult_(sb.ToString()) result = Dts.Results.Success Catch ex As Exception result = Dts.Results.Failure Call 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("IsFileVariable") Call result.Add("Algorithm") Call result.Add("ResultVariable") Call result.Add(IIf(Me.IsFileVariable, "FileVariable", "File")) 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.File) Then Throw New ApplicationException("Please specify File.") End If If String.IsNullOrEmpty(Me.ResultVariable) Then Throw New ApplicationException("Please select result variable.") End If result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category(" File")> _ <Description("Specifies if file is variable.")> _ <RefreshProperties(RefreshProperties.All)> _ Public Property IsFileVariable() As Boolean Get IsFileVariable = m_isFileVariable End Get Set(ByVal value As Boolean) If m_isFileVariable <> value Then m_isFileVariable = value m_file = String.Empty End If End Set End Property ' IsFileVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category(" File")> _ <Description("Select variable containing input file for checksum creation.")> _ <Variable()> _ Public Property FileVariable() As String Get FileVariable = m_file End Get Set(ByVal value As String) m_file = value End Set End Property ' FileVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category(" File")> _ <Description("Select input file for checksum creation.")> _ <Connection("FileConnectionType")> _ Public Property File() As String Get File = m_file End Get Set(ByVal value As String) m_file = value End Set End Property ' File ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Checksum")> _ <Description("Select checksum algorithm.")> _ Public Property Algorithm() As AlgorithmTypes Get Algorithm = m_algorithm End Get Set(ByVal value As AlgorithmTypes) m_algorithm = value End Set End Property ' Algorithm ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Checksum")> _ <Description("Select package variable to store checksum.")> _ <Variable()> _ Public Property ResultVariable() As String Get ResultVariable = m_resultVar End Get Set(ByVal value As String) m_resultVar = value End Set End Property ' ResultVariable #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private ReadOnly Property FileConnectionType() As String() Get FileConnectionType = New String() {"FILE"} End Get End Property ' FileConnectionType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetFilePath_(ByVal isVariable As Boolean, ByVal file As String) As String Dim result As String If isVariable Then result = GetVariable_(file) Else result = GetConnectionFile_(file) End If GetFilePath_ = result End Function ' GetFilePath_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get variable value. Private Function GetVariable_(ByVal varName As String) As String Dim result As String Dim vars As Variables Call Dts.VariableDispenser.LockOneForRead(varName, vars) Try result = vars(varName).Value.ToString() Finally Call vars.Unlock() End Try GetVariable_ = result End Function ' GetVariable_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetConnectionFile_(ByVal connName As String) As String Dim result As String result = Dts.Connections(connName).AcquireConnection(Nothing).ToString() GetConnectionFile_ = result End Function ' GetConnectionFile_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Store provided value in specified ResultVariable. Private Sub StoreResult_(ByVal value As Object) Dim vars As Variables Call Dts.VariableDispenser.LockOneForWrite(Me.ResultVariable, vars) Try vars(Me.ResultVariable).Value = value Finally Call vars.Unlock() End Try End Sub ' StoreResult_ #End Region ' Internals #Region "Attributes" Public Enum AlgorithmTypes MD5 SHA1 End Enum ' AlgorithmTypes Private m_isFileVariable As Boolean Private m_file As String Private m_algorithm As AlgorithmTypes Private m_resultVar As String #End Region ' Attributes End Class ' ScriptMain