Friday, January 28, 2011

SSIS script, Read and Write Variable functions

Script task to read and write variables.
To read
fileLoc = ReadVariable("FileFolderLoc")

To Write
WriteVariable("SubDirectoryObject", arr)

Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Dim vars As Variables
Dim emptyBytes(0) As Byte
Dim logMsg As String
Dim logCd As Integer

Try
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)

Try
result = vars(varName).Value
Catch ex As Exception
logMsg = "*** CANNOT READ VARIABLE " + varName + " ***"
logCd = 1001
Dts.Log(logMsg, logCd, emptyBytes)
Dts.Events.FireError(logCd, " ", logMsg, " ", -1)
Dts.TaskResult = ScriptResults.Failure
Throw ex
Finally
vars.Unlock()
End Try

Catch ex As Exception
logMsg = "*** CANNOT LOCK VARIABLE " + varName + " FOR READ ***"
logCd = 1002
Dts.Log(logMsg, logCd, emptyBytes)
Dts.Events.FireError(logCd, " ", logMsg, " ", -1)
Dts.TaskResult = ScriptResults.Failure
Throw ex
End Try

Return result
End Function

Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Dim vars As Variables
Dim emptyBytes(0) As Byte
Dim logMsg As String
Dim logCd As Integer

' If the variable you are updating is in the parent package,
' DO NOT list the variable in the child package and
' DO NOT include it in the package configuration.

Try
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)

Try
vars(varName).Value = varValue
Catch ex As Exception
logMsg = "*** CANNOT UPDATE VARIABLE " + varName + " WITH VALUE " + varValue.ToString + " ***"
logCd = 1003
Dts.Log(logMsg, logCd, emptyBytes)
Dts.Events.FireError(logCd, " ", logMsg, " ", -1)
Dts.TaskResult = ScriptResults.Failure
Throw ex
Finally
vars.Unlock()
End Try

Catch ex As Exception
logMsg = "*** CANNOT LOCK VARIABLE " + varName + " FOR WRITE ***"
logCd = 1004
Dts.Log(logMsg, logCd, emptyBytes)
Dts.Events.FireError(logCd, " ", logMsg, " ", -1)
Dts.TaskResult = ScriptResults.Failure
Throw ex
End Try
End Sub

No comments:

Post a Comment