Accessing the Variables collection in SSIS (2k5)

Question

Accessing the Variables collection in SSIS (2k5)

niall.baird

Hall of Fame

Points: 3468
More actions
February 16, 2011 at 3:40 pm

#246880

I have a little problem - I have an error handling routine, where I use a script task to cycle through all the variables contained in the package (both User:: and System::), putting them into name/value pairs and into a string, which eventually ends up in an ErrorHandler database. I thought all was going well, until I started to use this in anger (sorry...production) and found that the variable values being passed through were in fact the initial values that I used in development. For example, I have a variable "UserDatabaseName" which is set dynamically when the package starts - it was set to "Fred" in development, but in Production it should be either "MainCube", "AsiaCube" or something else.
Now, when my package errors & it goes into the error handler event, I can see the variable UserDatabaseName={MainCube} however inside the script task, the variable value is still "Fred".
This seems to be because the package is loading another copy of itself.
[Code]
Dim pkg As Package = app.LoadPackage("D:\Projects\MDXPerformance\MDXPerformance\MDXPerformance.dtsx", Nothing)
Dim vars As Variables = pkg.Variables
[/code]
I got that code from MSDN
Now, what I'd like to put out there to the community, is: Is there any way to cycle through the variables collection at run time? If so, how?
thanks in advance,
niall

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Well, yes and no.

First in the script task you have to expose the variables to the script task in the Script Task editor window, at the least as ReadOnlyVariables.

If you expose ALL of your variables this way, this script sample would let you loop through them:

Dim c As Integer

Dim d As Integer

c = Dts.Variables.Count

For d = 1 To c

MsgBox(CStr(Dts.Variables(d).Value))

Next

However, I usually use something more like:

msgbox( cstr(dts.variables("test1").value))

The other way to reference them, though afaik it only works in the script component as a rowsource for a datastream, is variables.<name>

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

niall.baird Hall of Fame Points: 3468 More actions · Answer 2

Thanks Craig - I'm actually trying to avoid having to expose each variable by name in the script task, as I'm trying to build a generic routine for error handling, to be put into each package's Event Handler for OnError - basically, if the package errors, I want to be able to collect all the variables/values and put them into a string which will end up in an email.

The problem is that if I expose the variables by name into the script task, we have a lot of 'configuration' type variables which our support team would like to have in email informing them that there's an issue/error with the package. It wouldn't be much of a generic routine if all our developers had to name each variable inside the script task - but if that's the only way to do it, then I guess that's what I'll have to do.

It strikes me as quite quirky that MS will allow you to view the collection and get the values, but they are the design time values, not the runtime values. I'm sure there's a way to do it, just not sure what it actually is :crazy:

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

niall.baird (2/16/2011)
Thanks Craig - I'm actually trying to avoid having to expose each variable by name in the script task, as I'm trying to build a generic routine for error handling, to be put into each package's Event Handler for OnError - basically, if the package errors, I want to be able to collect all the variables/values and put them into a string which will end up in an email.

Yeah, I'm familiar with that problem. However, you can't change the package's metadata at runtime (including component definitions), so as far as I know, noone's been able to work around that problem yet.

It strikes me as quite quirky that MS will allow you to view the collection and get the values, but they are the design time values, not the runtime values. I'm sure there's a way to do it, just not sure what it actually is :crazy:

If you do figure it out, you'll probably have a small herd of people asking you to convert it to being able to change flat file metadata on the fly, too. 🙂

On a side note, if your developers use an Object Variable (say, as a Recordset Destination for ADO foreach looping), that loop breaks... BADLY. Objects don't like being used in standard ways. So, even if you can get it working, your generic error handler is going to need to be very robust.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

niall.baird Hall of Fame Points: 3468 More actions · Answer 4

Hmmm.....thanks (I think 🙂 )

Just in case anyone wants to give it a go, here's the code I have that cycles through the collection and gives me all the design time values:

Public Sub Main()

Dim app As Application = New Application()

Dim x As VariableEnumerator

'http://msdn.microsoft.com/en-US/library/microsoft.sqlserver.dts.runtime.variables.getenumerator(v=SQL.90).aspx

Dim pkg As Package = app.LoadPackage("D:\Projects\MDXPerformance\MDXPerformance\MDXPerformance.dtsx", Nothing)

'Dim pkg As Package

Dim vars As Variables

'dts.VariableDispenser.LockForRead(dts.VariableDispenser.GetVariables

Dim strPkgName As String

'Dim vars As Variables = pkg.GetVariables

Dim i As Integer

Dim max As Integer

Dim strVars() As String

Dim strUserVarName As String

Dim strUserVarValue As String

Dim strSysVarName As String

Dim strSysVarValue As String

Dim UserMsg As String

Dim SysMsg As String

Dim isLocked As Boolean

Dim ErrString As String

Dim ErrNo As Int32

Dim SourceDescription As String

Dim SourceName As String

Dim OutputString As String

strPkgName = "SSIS Package=" & vars("System::PackageName").Value.ToString

max = pkg.Variables.Count

For i = 0 To max - 1

If vars.Item(i).Namespace.ToString = "User" Then

strUserVarName = vars.Item(i).Name

strUserVarValue = vars.Item(i).Value.ToString

UserMsg = UserMsg & strUserVarName & "=" & strUserVarValue & vbCrLf

OutputString = OutputString & UserMsg & ","

If strUserVarName = "DatabaseName" Then

MsgBox(strUserVarName & "=" & strUserVarValue)

End If

Else

strSysVarName = vars.Item(i).Name

strSysVarValue = vars.Item(i).Value.ToString

SysMsg = SysMsg & strSysVarName & "=" & strSysVarValue & vbCrLf

OutputString = OutputString & SysMsg & ","

End If

ErrNo = CInt(Dts.Variables("ErrorCode").Value)

ErrString = Dts.Variables("ErrorDescription").Value.ToString

SourceName = Dts.Variables("SourceName").Value.ToString

SourceDescription = Dts.Variables("SourceDescription").Value.ToString

Next i

OutputString = strPkgName & vbCrLf & "User Variables" & vbCrLf & UserMsg & vbCrLf & "System Variables" & vbCrLf & SysMsg

isLocked = vars.Locked

If isLocked = True Then

vars.Unlock()

End If

'UserMsg = "Error: " & ErrNo & vbCrLf & "Error Description: " & ErrString & vbCrLf & "Source Task: " & _

' SourceName & vbCrLf & SourceDescription & vbCrLf & UserMsg

'MsgBox(strPkgName & vbCrLf & "User Variables" & vbCrLf & UserMsg & vbCrLf & "System Variables" & vbCrLf & SysMsg)

Select Case Len(OutputString)

Case Is <= 3999