ErrorWriter, Error Logging OnError event

This code shows an ScriptTask, which is used as an global ErrorHandler in an SSiS Task.
It could be used also in Parent-Child architectures, where Tasks will be called by a Main Task.
The ErrorHandling will be controlled by several variables, which

Logging

LOG_Error As Integer = &H1
LOG_Warning As Integer = &H2
LOG_Info As Integer = &H4
LOG_Verbose As Integer = &H8
LOG_MsgBox As Integer = &H10
LOG_UserVars As Integer = &H100
LOG_SysVars As Integer = &H200

Used Variables

In a two level process (ParentChild), there is no need to set the ErrorLevel variable!
But in more deep processes, we control via this variable the behavior of the error catching mechanism from
bottom to top. So if we would have a 3 level architecture, and in the bottom task was an error, this would get the
CatchLevel = 1. In the middle task the variable would be CatchLevel = 2 and in to top most level, this would be
catchLevel = 3

Properties

Code

Code
'______________________________________________________________________
' This task is good for a package OnError handler. We could log here
' each error, which occurs in any of the defined packages
' If ther eis no AuditPkgExecKey or AuditTableProcessKey , we create a record in the
' AuditPkgExecution table by our own und write out the AuditPkgExecKey to a task local variable
' AuditPkgExecLocal, which should exist on Task level. So we are able to log errors even if there is
' no real environment
'
' Control variables
' Used Variables
' Must
' Logging: LogLevel
' varTaskSuccess : ReturnCode of the ChildTask
' PkgExecKeyLocal: Local variable to hold the InstanceGUID, if varImportID doesn't exist
' Could Be
' ErrorLevel (TaskLevel, RO) : Defines the ErrorLevel for which we catch the error, default = 0
' ErrorCatchLevel (Global, RW) : Globale variable, will be set to Level, if a OnErrorHandler runs
' Check this, if Level < CatchLevel (or CatchError = 0), we log the stuff
' The Level will be incremented by the errHandler hierarchy (first child will get 1)
' ErrorDisableDBLogging: (TaskLevel, RO) : Do not logging into database
' ErrorChild: If ErrorDisableDBLogging is true, this varibale holds the error message
' ErrorRethrow: Set System::Propagate to true or false,
' means the error will be thrown to the next upper caller
' ErrorTask: Holds the Stacktrace (PackjageNames) from bottom to top (revered in conjunction with java/C#)
' ErrorAdditionalVariables : Comma seperated list of variables to log
'
' Used User variables:
' varCS: Connection string for making the database connection and write the error into database
' varImportID : Key which combines the error with the package
' varRecordCounter: Count of records, if any to be updated in the PacakgeExecutionTable
'
' Used System variables:
' System::ErrorDescription
' System::ErrorCode
' System::SourceName
' System::SourceID
' System::PackageName
' System::TaskName (where the error occured)
' System::ExecutionInstanceGUID
'______________________________________________________________________
Imports System
Imports System.Text
Imports System.Data
Imports System.Math
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private mLog As MyLog
Private mPkgExecID As String = ""
Private mConnectionString As Dictionary(Of String, String)
Private mCatchLevel As Integer
Private mLevel As Integer
Private mDatabase As String = ""
Private mIsInsert As Boolean
Private mPropagate As Boolean

Public Sub Main()
Try
mLog = New MyLog(Dts, "Logging") ' This is the first and important stuff

If Me.ErrorDoNotHandle Then ' This variable must be set to recognize, that there is a parentTask
Me.Propagate = True
Exit Sub
End If

If Me.alreadyCatched() Then
'MsgBox(String.Format("ErrorCatchLevel={0}\nErrorLevel={1}\nErrorChild={2}\n{3}", Me.ErrorCatchLevel, Me.ErrorLevel, Me.ErrorChild, mLog.UserVariables(vbCrLf)).Replace("\n", vbCrLf))
Exit Sub ' Ok do noting, if we had already catched this error in a lower package
End If

'*********************
' Get the needed variables
mConnectionString = New Dictionary(Of String, String) ' Connection strings
mConnectionString.Add(Me.Database, Me.ConnectionString) ' Store this for the database
mPkgExecID = Me.PkgExecID ' Get the ImportID or the local GUID (System::ExecutionInstanceGUID)

'*********************
' Show this via MsgBox, if enabled
mLog.log(mLog.LOG_MsgBox, _
New StringBuilder("").AppendFormat("ErrID : {0}, ExecID : {1}", Me.ErrorCode, Me.PkgExecID).ToString, _
"{0}\nException\n{1}\nUservariables:\n{2}", Me.ErrorText, Me.ErrorDescription, mLog.UserVariables _
)

'*********************
' Log also as Warning
mLog.log(mLog.LOG_Warning, "", "[Main Error handler " + CStr(Me.ErrorCode) + "] " + Me.ErrorDescription)

'*********************
' Ok, write the stiff to database
If Not Me.ErrorDisableDBLogging Then ' Log the stuff to the database, if enabled
If Me.IsInsert Then
Call Me.Insert_AuditPkgExecution(mDatabase) ' Create a Record
mIsInsert = False ' Only once, afterwards, we use this record for update
Else
Call Me.Update_AuditPkgExecution(mDatabase) ' Update the record
End If
Call Me.LogErrorToDatabase(mDatabase) ' Log th error
End If
Catch ex As Exception
MsgBox(ex.ToString, , "Attention :: Unexpected error in Main.OnError handler [" + mLog.Taskname + "]")
mLog.log(mLog.LOG_Error, "", "Attention :: Unexpected error in Main.OnError {0}\n{1}", ex.ToString, ex.InnerException)
Finally
Me.ErrorChild = Me.ErrorText ' If there is a parent package which host this variable, set it to the error task string
Me.ErrorTask = mLog.StackTrace() ' Append the Original Name to the Call Queue
Me.TaskSuccess = False ' Ok, ther is an error => varTaskSuccess must be false
Me.ErrorCatchLevel = mLevel ' Return the Level to decide upwards, if we should handle the error again or not
End Try

Dts.TaskResult = Dts.Results.Success
End Sub

Public Function alreadyCatched() As Boolean
'_______________________________________________________________________________
' This stuff is responsible for logging. Each involved errorHandler looked,
' if he should log something.
' This decission would be done by checking the own value Level against ErrorLevel
' If Level > ErrorLevel, do logging
' other wise log nothing but set the ErrorLevel to the own level
' The ErrorWrite on the top most calling level shold have a Level of 0,
'so the next error could do the same procedure
'_______________________________________________________________________________

mCatchLevel = Me.ErrorCatchLevel
mLevel = Me.ErrorLevel
If mCatchLevel = -1 Then
alreadyCatched = False ' And log the stuff
If Not Me.ErrorIsIncremented Then mLevel += 1
ElseIf mCatchLevel > mLevel Then
alreadyCatched = True ' Ok, something smaller log already, but we log too
Else
If Not Me.ErrorIsIncremented Then mLevel += 1
alreadyCatched = False ' Do logging
End If
Me.ErrorIsIncremented = True ' Only increment once per level and Task
End Function

Public Property TaskSuccess() As Boolean
Get
Return Convert.ToBoolean(mLog.Read("varTaskSuccess", "True"))
End Get
Set(ByVal value As Boolean)
mLog.Write("varTaskSuccess", value)
End Set
End Property
Public ReadOnly Property IsInsert() As Boolean
Get
mPkgExecID = Me.PkgExecID
Return mIsInsert
End Get
End Property
Public ReadOnly Property RecordCount() As String
Get
Return mLog.Read("varRecordCounter", "null").ToString()
End Get
End Property
Public ReadOnly Property ErrorDoNotHandle() As Boolean
Get
Return Convert.ToBoolean(mLog.Read("ErrorDoNotHandle", "false").ToString())
End Get
End Property

Public ReadOnly Property ErrorDisableDBLogging() As Boolean
Get
Return Convert.ToBoolean(mLog.Read("ErrorDisableDBLogging", "false"))
End Get
End Property
Public ReadOnly Property ErrorDescription() As String
Get
Return mLog.Read("System::ErrorDescription").ToString
End Get
End Property
Public ReadOnly Property ErrorCode() As String
Get
Return Convert.ToString(mLog.Read("System::ErrorCode"))
End Get
End Property
Public ReadOnly Property ErrorText() As String
Get
'Return New StringBuilder("").AppendFormat("Error in Task : '{0}.{1}' [{2}]", mLog.Read("System::PackageName").ToString, mLog.Read("System::SourceName").ToString, mLog.Read("System::SourceID").ToString).ToString
Return New StringBuilder("").AppendFormat("Error in Task : '{0}.{1}' [{2}]", mLog.StackTrace, mLog.Read("System::SourceName").ToString, mLog.Read("System::SourceID").ToString).ToString
End Get
End Property
Public Property ErrorTask() As String
Get
Return mLog.Read("ErrorTask", mLog.Read("System::PackageName", "Unkown").ToString).ToString
End Get
Set(ByVal value As String)
mLog.Write("ErrorTask", value)
End Set
End Property
Public Property Propagate() As Boolean
Get
Return Convert.ToBoolean(mLog.Read("ErrorRethrow", "True"))
End Get
Set(ByVal value As Boolean)
mLog.Write("System::Propagagte", value)
End Set
End Property
Public ReadOnly Property ConnectionString() As String
Get
Return mLog.Read("varCS").ToString()
End Get
End Property
Public ReadOnly Property Database() As String
Get
If mDatabase.Length <= 0 Then mDatabase = mLog.Read("DBDatabase").ToString()
Return mDatabase
End Get
End Property
Public ReadOnly Property PkgExecID() As String
Get
If mPkgExecID.Length <= 0 Then
mPkgExecID = mLog.Read("varImportID", mLog.Read("PkgExecKeyLocal").ToString()).ToString() ' Get the ImportID or the local GUID (System::ExecutionInstanceGUID)
If mPkgExecID.Length <= 0 Then
mPkgExecID = mLog.Read("System::ExecutionInstanceGUID", "").ToString() ' => get the PackageInstanceGUID
Call Me.mLog.Write("PkgExecKeyLocal", mPkgExecID) ' Remeber the GUID locally for other error loggings
mIsInsert = True
End If
End If
Return mPkgExecID
End Get
End Property
Public Property ErrorChild() As String
Get
Return mLog.Read("ErrorChild", "").ToString()
End Get
Set(ByVal value As String)
mLog.Write("ErrorChild", value)
End Set
End Property
Public Property ErrorIsIncremented() As Boolean
Get
Return Convert.ToBoolean(mLog.Read("ErrorIsIncremented", "false"))
End Get
Set(ByVal value As Boolean)
mLog.Write("ErrorIsIncremented", value)
End Set
End Property
Public ReadOnly Property ErrorLevel() As Integer
Get
Return Convert.ToInt32(mLog.Read("ErrorLevel", "0"))
End Get
End Property
Public Property ErrorCatchLevel() As Integer
Get
Return Convert.ToInt32(mLog.Read("ErrorCatchLevel", "-1"))
End Get
Set(ByVal value As Integer)
mLog.Write("ErrorCatchLevel", value)
End Set
End Property

Public Sub Insert_AuditPkgExecution(ByVal szCSType As String)
'___________________________________________________________________
' Insert the Data into the database
'___________________________________________________________________
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim szCmd As String

Try
szCmd = String.Format( _
"INSERT INTO [dbo].[AuditPkgExecution] (" + _
" [APE_PkgExecID] " + _
",[APE_ParentPkgExecID] " + _
",[APE_PkgGUID] " + _
",[APE_PkgVersionGUID] " + _
",[APE_PkgVersionMajor] " + _
",[APE_PkgVersionMinor] " + _
",[APE_Taskname] " + _
",[APE_StartDateTime] " + _
",[APE_EndDateTime] " + _
",[APE_ProcessCount] " + _
",[APE_Success]" + _
") VALUES (" + _
GUID2Str(Me.PkgExecID) + _
", null" + _
", " + GUID2Str(mLog.Read("System::ExecutionInstanceGUID").ToString()) + _
", " + GUID2Str(mLog.Read("System::VersionGUID").ToString()) + _
", " + mLog.Read("System::VersionMajor").ToString() + _
", " + mLog.Read("System::VersionMinor").ToString() + _
", '" + mLog.Read("System::PackageName").ToString() + "'" + _
", convert(datetime, '" + Format(mLog.Read("System::StartTime"), "dd.MM.yyyy HH:mm:ss") + "', 104)" + _
", 0" + _
", null" + _
", 0" + _
")" _
)

conn = New SqlConnection(Me.mConnectionString(szCSType))
cmd = New SqlCommand(szCmd)
mLog.log(mLog.LOG_Verbose, "", "Insert_AuditPkgExecution : Cmd = {0}", szCmd)

conn.Open()
cmd.Connection = conn
cmd.CommandType = System.Data.CommandType.Text
cmd.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
mLog.log(mLog.LOG_Error, "", "Insert_AuditPkgExecution : {0}\nCmd = {1}\n\n{2}\n{3}", ex.Message, szCmd, ex.ToString, ex.InnerException)
MsgBox(String.Format("Insert_AuditPkgExecution : {0}\nCmd = {1}\n\n{2}\n{3}\nCS={4}", ex.Message, szCmd, ex.ToString, ex.InnerException, Me.mConnectionString(szCSType)).Replace("\n", vbCrLf), , "Unexpected Error in ErrorHandler")
End Try
End Sub

Public Sub Update_AuditPkgExecution(ByVal szCSType As String)
'___________________________________________________________________
' Update the Data into the database
'___________________________________________________________________
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim szCmd As String

Try
szCmd = String.Format( _
"UPDATE [dbo].[AuditPkgExecution] " + _
" SET " + _
" [APE_EndDateTime] = GETDATE() " + _
",[APE_ProcessCount] = {0} " + _
",[APE_Success] = 0 " + _
"WHERE [APE_PkgExecID] = {1} ", _
Me.RecordCount(), _
GUID2Str(Me.PkgExecID) _
)

conn = New SqlConnection(Me.mConnectionString(szCSType))
cmd = New SqlCommand(szCmd)
mLog.log(mLog.LOG_Verbose, "", "Update_AuditPkgExecution : Cmd = {0}", szCmd)

conn.Open()
cmd.Connection = conn
cmd.CommandType = System.Data.CommandType.Text
cmd.ExecuteNonQuery()
cmd.Dispose()
conn.Close()
Catch ex As Exception
mLog.log(mLog.LOG_Error, "", "Update_AuditPkgExecution : {0}\nCmd = {1}\n\n{2}\n{3}", ex.Message, szCmd, ex.ToString, ex.InnerException)
'MsgBox(String.Format("Update_AuditPkgExecution : {0}\nCmd = {1}\n\n{2}\n{3}\nCS={4}", ex.Message, szCmd, ex.ToString, ex.InnerException, Me.mConnectionString(szCSType)).Replace("\n", vbCrLf), , "Unexpected Error in ErrorHandler")
End Try
End Sub

Sub LogErrorToDatabase(ByVal szCSType As String)
' Insert the Data into the database
'___________________________________________________________________
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim szCmd As String
Dim reader As SqlDataReader

Try

szCmd = String.Format( _
"INSERT INTO [dbo].[AuditError] " + _
" ([AE_PkgExecID] " + _
" ,[AE_FileProcessID] " + _
" ,[AE_Timestamp] " + _
" ,[AE_ErrorID] " + _
" ,[AE_ErrorCol] " + _
" ,[AE_ErrorText] " + _
" ,[AE_ErrorDescription] " + _
" ,[AE_ErrorData]) " + _
" VALUES " + _
" ({0}, {1}, {2}, {3}, {4}, '{5}', '{6}', '{7}')", _
GUID2Str(Me.PkgExecID), _
"null", _
"getdate()", _
Me.ErrorCode.ToString, _
"0", _
Me.ErrorText.Replace("'", "''"), _
Left(Me.ErrorDescription.Replace("'", "''"), 1024), _
String.Format("User Variables: {0}", mLog.UserVariables.Replace("'", "''")) _
)

conn = New SqlConnection(Me.mConnectionString(szCSType))
cmd = New SqlCommand(szCmd)

mLog.log(mLog.LOG_Verbose, "", "LogErrorToDatabase : Cmd = {0}", szCmd)

conn.Open()
cmd.Connection = conn
cmd.CommandType = System.Data.CommandType.Text
cmd.ExecuteNonQuery()
cmd.Dispose()
conn.Close()
Catch ex As Exception
mLog.log(mLog.LOG_Error, "", "LogErrorToDatabase : {0}\nCmd = {0}\nCmd = {1}\n\n{2}\n{3}\n{4}", ex.Message, szCmd, ex.ToString, ex.InnerException, Me.mConnectionString(szCSType))
'MsgBox(String.Format("Update_AuditPkgExecution : {0}\nCmd = {1}\n\n{2}\n{3}\nCS={4}", ex.Message, szCmd, ex.ToString, ex.InnerException, Me.mConnectionString(szCSType)).Replace("\n", vbCrLf), , "Unexpected Error in ErrorHandler")
End Try
End Sub

Function GUID2Str(ByVal szGUID As String) As String
Return "'" + szGUID.Replace("{", "").Replace("}", "") + "'"
End Function
End Class

' ********************************************************************
' ********************************************************************
' Standard Logging
' mLog = New MyLog(Dts, "Logging")
' String.Format("User Variables: {0}", mLog.UserVariables)
' ********************************************************************
' ********************************************************************
Class MyLog
Inherits MyVariables
'================================================
' DataFlow
'Protected mEvents As IDTSComponentMetaData90
'Protected mMain As ScriptMain
'Protected mVars As IDTSVariables90
'================================================
' ControlFlow
'Protected mEvents As IDTSComponentEvents
'Protected mMain As Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel
'Protected mVars As Variables

Private mSysLogLevel As Integer
Private mID As Integer = 0
Private mShowMsgbox As Boolean = False
'Private mTaskname As String = ""
Private mStackTrace As String = ""

Public Const LOG_Error As Integer = &H1
Public Const LOG_Warning As Integer = &H2
Public Const LOG_Info As Integer = &H4
Public Const LOG_Verbose As Integer = &H8
Public Const LOG_MsgBox As Integer = &H10
Public Const LOG_UserVars As Integer = &H100
Public Const LOG_SysVars As Integer = &H200

'_____________________________________________________________________
' Constructor
'_____________________________________________________________________
Sub New(ByRef main As Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel, ByVal LogLevelVariable As String)
MyBase.New(main)
Try

mEvents = mMain.Events '====== ControlFlow
'mEvents = mMain.ComponentMetaData '====== DataFlow

mSysLogLevel = CInt(Me.Read(LogLevelVariable, "0"))
Catch ex As Exception
mEvents.FireError(1, Me.ToString, ex.ToString, "", 0)
End Try
End Sub

'_____________________________________________________________________
' Property SysLogLevel
'_____________________________________________________________________
Public Property SysLogLevel() As Integer
Get
SysLogLevel = mSysLogLevel
End Get
Set(ByVal value As Integer)
mSysLogLevel = value
End Set
End Property

'_____________________________________________________________________
' Some explicit properties
'_____________________________________________________________________
ReadOnly Property PackageName() As String
Get
Return Me.Read("System::PackageName").ToString
End Get
End Property
ReadOnly Property Taskname() As String
Get
Return Me.Read("System::TaskName").ToString
End Get
End Property
ReadOnly Property FullTaskname() As String
Get
Return Me.StackTrace + "." + Me.Read("System::TaskName").ToString
End Get
End Property
ReadOnly Property isSysVars() As Boolean
Get
isSysVars = ((mSysLogLevel And Me.LOG_SysVars) > 0)
End Get
End Property
ReadOnly Property isUserVars() As Boolean
Get
isUserVars = ((mSysLogLevel And Me.LOG_UserVars) > 0)
End Get
End Property
ReadOnly Property isMsgBox() As Boolean
Get
isMsgBox = ((mSysLogLevel And Me.LOG_MsgBox) > 0)
End Get
End Property
Public ReadOnly Property StackTrace() As String
Get
If mStackTrace.Length = 0 Then
mStackTrace = Me.Read("ErrorTask").ToString()
If mStackTrace.Length > 0 Then mStackTrace = Me.Read("System::PackageName").ToString() + "/" + mStackTrace Else mStackTrace = Me.Read("System::PackageName").ToString()
End If
Return mStackTrace
End Get
End Property
Public ReadOnly Property UserVariables(Optional ByVal sep As String = ",") As String
Get
Return Me.VariableList(sep).ToString()
End Get
End Property

'_____________________________________________________________________
' Function setID
' Description
' This function sets the internal id field which will be used instead
' of the LogLevel value for the Identifier printed out
' The value will be resetted after each Write function call.
' We return a reference of us back to the caller to mkae suche constructs possible :
' myInstance.SetID(&h1000).Write(MyClass.LogError, "MySource", "Test")
'_____________________________________________________________________
Function setID(ByVal id As Integer) As MyLog
mID = id
Return (Me)
End Function

'_____________________________________________________________________
' Function log
' Description
' This function logs the given Message, which could be build up like the
' WriteFunction in the System.Console with optional paramters {0} .. {n}
' LogLevel : Print out only if this level is included in the binary SysLogLevel
' Message : Message string
' Source : Which Task should be print out in the message
' parameter : Optional Values to build into the message
'_____________________________________________________________________
Sub log(ByVal logLevel As Integer, ByVal source As String, ByVal message As String, ByVal ParamArray parameter() As Object)
Dim Id As Integer

If (logLevel And mSysLogLevel) > 0 Then
If parameter.Length > 0 Then message = New StringBuilder("").AppendFormat(message, parameter).ToString
If mID = 0 Then Id = logLevel Else Id = mID ' if we had no internal id, use the external
If source.Length = 0 Then source = Me.Taskname ' If we had no source, get it from system
message = message.Replace("\n", vbCrLf)

If logLevel = Me.LOG_Info Or logLevel = Me.LOG_Verbose Then
mEvents.FireInformation(Id, source, message, "", 0, True)
ElseIf logLevel = Me.LOG_Warning Then
mEvents.FireWarning(Id, source, message, "", 0)
ElseIf logLevel = Me.LOG_Error Then
mEvents.FireError(Id, source, message, "", 0)
ElseIf logLevel = Me.LOG_MsgBox Then
MsgBox(String.Format("Stack : {0}\n{1}", Me.FullTaskname, message.Replace(",", vbCrLf)).Replace("\n", vbCrLf), , Me.PackageName + " :: ErrorID = " + CStr(Id))
ElseIf logLevel = Me.LOG_UserVars Then
mEvents.FireInformation(Id, source, Me.UserVariables(), "", 0, True)
End If
mID = 0 ' Reset the internal ID
End If
End Sub

Function sprintf(ByVal message As String, ByVal ParamArray parameter() As Object) As String
Dim stringBuilder As New StringBuilder("")
sprintf = stringBuilder.AppendFormat(message, parameter).ToString()
End Function

End Class

'___________________________________________________________
' Class for handling variables in ControlFlow
' This should be instanciated the very fist step
'___________________________________________________________
Class MyVariables
'================================================
' DataFlow
'Private mEvents As IDTSComponentMetaData90
'Private mMain As ScriptMain
'Private mVars As IDTSVariables90

'================================================
' ControlFlow
Protected mEvents As IDTSComponentEvents
Protected mMain As Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel
Protected mVars As Variables

Private mVarNames As List(Of String)
Private mAddVarNames As String = ""

'___________________________________________________________
' Constructor
'___________________________________________________________
Sub New(ByRef main As Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel)

mMain = main ' '====== ControlFlow
mEvents = mMain.Events
mVarNames = New List(Of String)

'mMain = main ' '====== DataFlow
'mEvents = mMain.ComponentMetaData

Call Me.InitVariableNames() ' First of all, get the ReadOnly and ReadWriteVariables of the task and all other defined variables (ErrorAdditionalVariables)

Me.VariableNames = Me.Read("ErrorAdditionalVariables", "").ToString()
Me.VariableNames = "ErrorLevel,ErrorCatchLevel,ErrorDisableDBLogging,ErrorChild,ErrorRethrow,ErrorDoNotHandle,ErrorTask"
End Sub

'___________________________________________________________
' Returns back the initial variable names
' or if compareName is given, the we return back each name
' which contains CompareName
'___________________________________________________________
Property VariableNames(Optional ByVal CompareName As String = "") As String
Get
If CompareName.Length = 0 Then
VariableNames = String.Join(",", mVarNames.ToArray())
Else
Dim result As String = ""
For Each varName As String In mVarNames
If varName.Contains(CompareName) Then
If result.Length = 0 Then result = varName Else result = result & "," & varName
End If
Next
VariableNames = result
End If
End Get
Set(ByVal value As String)
Dim v() As String = value.Split(","c)
For Each varName As String In value.Split(","c)
If Not mVarNames.Contains(varName) Then
mVarNames.Add(varName)
End If
Next
Me.getVars()
End Set
End Property

'___________________________________________________________
' Returns back the initial variable collection
'___________________________________________________________
ReadOnly Property Variables() As Variables
Get
Variables = Me.getVars()
End Get
End Property

'___________________________________________________________
' Returns all given varibales, also the additional ones back
' as a stringBuilder variable.
'___________________________________________________________
Public ReadOnly Property VariableList(Optional ByVal sep As String = ",") As StringBuilder
Get
Dim sb As StringBuilder = New StringBuilder()
Dim localSep As String = ""
Try
Me.getVars() ' Rebuild the variables collection
For Each varName As String In mVarNames
If varName.Length > 0 Then
If mVars.Contains(varName) Then
Dim var As Variable = mVars(varName)
sb.Append(String.Format("{0} = {1} [DataType: {2}; EvaluateAsExpression: {3}]", var.QualifiedName, var.Value.ToString(), var.DataType.ToString(), var.EvaluateAsExpression.ToString())).Append(localSep)
Else
sb.AppendFormat("{0} doesn't exist", varName).Append(localSep)
End If
localSep = sep
End If
Next
Catch ex As Exception
sb.Append("Error during iteration of variables : " + ex.Message)
End Try
Return sb
End Get
End Property

'_________________________________________________________________________________
' Read variables
' Parameter
' varname : Variablename, must be in the read or write section of the task
' If you want to query system vars, you must use the syntax 'System::<defined varname>'
' checkForExistance : Bool var to check or not before using the vars
'_________________________________________________________________________________
Function Read(ByVal varName As String, Optional ByVal DefaultValue As Object = Nothing, Optional ByVal NoExisting As String = "", Optional ByVal checkForExistance As Boolean = True) As Object
Dim result As Object
Dim var As Variable

Try
If Not checkForExistance Or mMain.VariableDispenser.Contains(varName) Then
mMain.VariableDispenser.Reset()
Call mMain.VariableDispenser.LockOneForRead(varName, mVars)
result = mVars(varName).Value ' Read variable
Call mVars.Unlock()
Else
If Not mVarNames.Contains(varName) Then
mEvents.FireWarning(&H1001, "ReadVariable", "Warning variable '" + varName + "' doen't exist in the collection", "", 0)
End If
If Not DefaultValue Is Nothing Then result = DefaultValue Else result = NoExisting
End If
Catch ex As Exception
mEvents.FireInformation(&H1002, "ReadVariable", "Error reading var " + varName + " : " + ex.Message, "", 0, True)
Throw ex
End Try

If Not DefaultValue Is Nothing Then
If TypeOf (result) Is String Then
If Len(Trim(result.ToString)) = 0 Then
result = DefaultValue
End If
End If
End If

Return result
End Function ' ReadVariable

'_________________________________________________________________________________
' Write variables
' Parameter
' varname : Variablename, must be in the read or write section of the task
' If you want to query system vars, you must use the syntax 'System::<defined varname>'
' value: Varibale value as Object to write to
' checkForExistance : Bool var to check or not before using the vars
'_________________________________________________________________________________
Function Write(ByVal varName As String, ByVal value As Object, Optional ByVal checkForExistance As Boolean = True) As Object
Dim result As Object
Dim var As Variable

Try
If Not checkForExistance Or mMain.VariableDispenser.Contains(varName) Then
mMain.VariableDispenser.Reset()
Call mMain.VariableDispenser.LockOneForWrite(varName, mVars)
mVars(varName).Value = value ' Write variable
Call mVars.Unlock()
Else
If Not mVarNames.Contains(varName) Then
mEvents.FireWarning(&H1001, Me.ToString, "Warning variable '" + varName + "' doen't exist in the collection", "", 0)
End If
End If
Catch ex As Exception
mEvents.FireInformation(&H1002, Me.ToString, "Error write var " + varName + " : " + ex.Message, "", 0, True)
Throw ex
End Try
Return result
End Function ' ReadVariable

'___________________________________________________________
' Check, if a variable exists
'___________________________________________________________
Function CheckForVariable(ByVal varName As String) As String
If mMain.VariableDispenser.Contains(varName) Then
Return varName + " exists"
Else
Return varName + " doesn't exists"
End If
End Function

'___________________________________________________________
' Get the initial Variable names
' This must be the first call, becuase otherwise, a read or write
' will destry the collection and/or leads into deadlocks ....
'___________________________________________________________
Private Sub InitVariableNames()
mVarNames.Clear()
For Each var As Variable In mMain.Variables
mVarNames.Add(var.Name)
Next
Me.getVars()
End Sub

'___________________________________________________________
' Recreates the initial variable collection
' Important, this function must be calle ddirectly after the
' InitVariableNames
'___________________________________________________________
Protected Function getVars() As Variables
Dim varName As String
Try
If Not mVars Is Nothing Then
If mVars.Locked Then mVars.Unlock()
Else
If mMain.Variables.Locked Then mMain.Variables.Unlock()
End If

For Each varName In mVarNames
If varName.Length > 0 And mMain.VariableDispenser.Contains(varName) Then
Call mMain.VariableDispenser.LockForRead(varName)
End If
Next
Call mMain.VariableDispenser.GetVariables(mVars)
getVars = mVars
Catch ex As Exception
mEvents.FireError(1, Me.ToString, String.Format("Error during lock of variable '{0}' : {1}", varName, ex.ToString), "", 0)
End Try
End Function
End Class


Submitted by	Category	Review Cycle	.	Public
Joachim Mutter/sysarc on 06/06/2008 at 02:18 PM	SSiS\Code