Imports System
Imports System.Data
Imports System.Math
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()
Dim sRowCount As String = SnippetParameter("User::RowCount", "", "").ToString
Dim sTotalRowSource As String = SnippetParameter("User::TotalRowSource", "", "").ToString
Dim sEmailBodyText As String = SnippetParameter("User::EmailBodyText", "", "")
sEmailBodyText = "Payee Load Verification:" _
& vbCrLf & " There are " & sRowCount & " records in the table DimPayee." _
& vbCrLf & " There are " & sTotalRowSource & " records in the source tables ST_STDNT_A, AR_CUSTOMER_A, FIP_VENDOR, EMPLOYEE_A and NON_EMPLOYEE_A." _
& vbCrLf & " If the record counts are not equal - Please Research" _
& vbCrLf & vbCrLf & sEmailBodyText
WriteVariable("User::EmailBodyText", sEmailBodyText)
Dts.TaskResult = ScriptResults.Success
End Sub
#Region "Helper functions"
Private Property SnippetParameter(ByVal sVarName As String, ByVal sConnName As String, ByVal sConnPropName As String) As Object
Get
Dim oRetVal As Object
If String.IsNullOrEmpty(sConnName) = False Then
Dim oCon As ConnectionManager
oCon = Dts.Connections(sConnName)
If String.IsNullOrEmpty(sConnPropName) Then
sConnPropName = "ConnectionString"
ElseIf sConnPropName = "<object>" Then
Return oCon
End If
oRetVal = oCon.Properties(sConnPropName).GetValue(oCon)
ElseIf String.IsNullOrEmpty(sVarName) = False Then
oRetVal = ReadVariable(sVarName)
Else
oRetVal = Nothing
End If
Return oRetVal
End Get
Set(ByVal oValue As Object)
If String.IsNullOrEmpty(sConnName) = False Then
Dim oCon As ConnectionManager
oCon = Dts.Connections(sConnName)
If String.IsNullOrEmpty(sConnPropName) Then
sConnPropName = "ConnectionString"
End If
oCon.Properties(sConnPropName).SetValue(oCon, oValue)
ElseIf String.IsNullOrEmpty(sVarName) = False Then
WriteVariable(sVarName, oValue)
End If
End Set
End Property
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables = Nothing
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
If Not String.IsNullOrEmpty(varName) AndAlso Dts.VariableDispenser.Contains(varName) Then
Dim vars As Variables = Nothing
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Else
result = Nothing
End If
Catch ex As Exception
Throw ex
End Try
Return result
End Function
#End Region
End Class |
|
Properties
