how to get total number of variables using scripttask

I want to loop thru all the variables in my package and set number of variables that had variable-name begin w/LOCAL, so I can use the name to generate a dynamic SQL query for the next EXEC SQL Task. any one know how to do this.

Use the count property on the variables collection.|||

that is what I did, Dts.Varaiables.count, but it did not return the right numbers. I have over 10 variables and it return 1.

|||

Have you added your 10+ variables to either the ReadOnlyVariables or ReadWriteVariables list? The Dts.Variables collection is generated based on those preset list of variables.

Thanks,
Patrik

|||

no, I did not.

OK, let me back out a little bit, I try to use the event handler as a trigger for my custom audit logging, for each task when I am done, I want to use a script to find out what is in my variables pool, loop thru them and build a query, (“select count(*) from ‘[“ + @.variable1), and then the next execute SQL task will execute this and return the count to a variable so I can have the OnPostExecute event write out the record counts, so I only have 1 variable “MySQL” in the ReadWriteVariables list, since what I want is when I create a new package, all I have to do is to add or remove local variables, no code changes is need it. if I have to put in all the variables, then I defeat my intention, is there a work around for this? Any suggestion is greatly appreciated.

|||

What sort of processing are you trying to achieve with the Script Task? Which variables are you using and looking for in the loop? Is the collection of variables you're interested in always the same? If you don't fill in the ReadOnlyVariables and ReadWriteVariables properties you can do the work using the VariableDispenser (http://msdn2.microsoft.com/de-de/library/microsoft.sqlserver.dts.runtime.variabledispenser.aspx); however, you would still need to know the name of the variables you're interested in reading.

If you're using the Script Task just to create a SQL command statement for the Execute SQL Task you could look at using expressions (http://msdn2.microsoft.com/en-us/library/ms141214.aspx).

Cheers,
Patrik

|||

Hi..

You can read all variables by using ActiveX Script Task instead of using Script Task.

Function Main()
Dim oVal

Dim isSystem, varType, varName
Dim str
MSGBOX "Total Variable = " & DTSGlobalVariables.Count

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

MSGBOX varName & " / " & varType & " / " & "System:" & isSystem

Next
Main = DTSTaskExecResult_Success

End Function

HTH

ADConsulting / SQLLeader.com / Daeseong Han