SSIS Reading all the user defined variables

In an SSIS scripttask you are not able to loop trough all the user defined variables if you don't know the name of the variable. I created a workaround.

Step 1:
create an ActiveX scripttask with the following script:'GETVARS.SCR

'This function gets all the user defined variables from a SSIS package
'(except ado Objects) and put them in a flatfile
'This flatfile can be
used to transfer the variables to a LOG file
'in the database
'20081030
John Minkjan


Function Main()
Dim oVal
Dim isSystem, varType,
varName, varValue
Dim str, FileName, filesys, filetxt

Const
ForReading = 1, ForWriting = 2, ForAppending = 8

Set filesys =
CreateObject("Scripting.FileSystemObject")

FileName = "c:\varfile_"&
DTSGlobalVariables("System::PackageName").Value &".txt"

Set filetxt
= filesys.OpenTextFile(FileName, ForWriting, True)

'Put in a header
filetxt.WriteLine("varName;varType;varValue")

For each oVal in
DTSGlobalVariables
isSystem = oVal.SystemVariable
varType =
oVal.DataType
varName = oVal.QualifiedName
varValue = oVal.Value


if not isSystem then
if varType <> 13 then '(Ado Objects)

if varValue = null or varValue = "" then
varValue = "NULL"
end
if

filetxt.WriteLine(Cstr(varName) &";" & Cstr(varType) &
";" & Cstr(varValue))

end if
end if

Next

filetxt.Close

Main = DTSTaskExecResult_Success

End
Function

Step 2:
Use a data flow to pull the flatfile back into the database for logging:

Till Next Time