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

SSIS Variables in procedures

Say you have a procedure like this on your MSSQL db which you want to call from SSIS. It has an input and output parameter which we want to use: In SSIS we make two variables:

Add an Execute SQL task to your Sequence Container
In the properties set the name, description and connection. For the SQLstatement put:


At the parameter mapping set direction and Parameter Name. The Parameter Name should a sequence starting at 0. The sequence should correpond with the order of the question marks in in your SQL statement.

Run your package (F5) to see if it works


Till Next Time