I have created a task that executes a simple query:
select count(*) from ifsmessages

Within this task I have created an output parameter called gMsgCount with a data type of int. I have given it a parameter type of Row Value.

Next I created an ActiveX object with VBScript language. Eventually I want to create a DTS package that allows the user to execute or not execute the package depending on the value returned in gMsgCount using a YES/NO message box. To test the running of the task and the output value I have written the following:

Function Main()
msgbox "The number of records in table IFSMessages is: " & DTSGlobalVariables("gMsgCount").Value, VBYesNo, "IFSMessages Count"
End Function

When I execute the ActiveX object step I get a VBScript error saying "Variable uses an automation type not supported in VBScript." What am I missing?

--
Message posted via http://www.sqlmonster.com

OK Here is what I did and it works

select count(*) as Amount from Authors

Assigned the ouput (Row Value) to a GV

In an Active Script task I did exactly as you.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Robert Richards via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote

Why put Vbyesno at the end?
if you want users to choose either Yes/No, you should use

Dim quest
quest = Msgbox ("Are you sure?", VBYesNo)
if quest = vbyes then
....
Else
....

Ed

"Robert Richards via SQLMonster.com" wrote:

I am still getting the same error. This is what I have verbatim for my script:

Function Main()
Dim resp

resp = MsgBox("The row count in table IFSMessages is: " & DTSGlobalVariables("gMsgCount").Value, VBYesNo)

if resp = VBYes then
'execute package
else
'do not execute package
end if
Main = DTSTaskExecResult_Success
End Function

When I execute the ActiveX object step I get a VBScript error saying "Variable uses an automation type not supported in VBScript."

Within the ActiveX Script Task properties, Package Object Browser under Global Variables I am able to see the output variable "gMsgBox" that I created in Execute SQL Task.

Within Execute SQL Task my SQL Statement verbatim is:
select count(*) as msgcount from ifsmessages

When I select the Parameters button and then the Output parameters tab, I have Row value output parameter type option selected. Below that I have in Parameter Mapping a parameter of "msgcount" and Output Global Variables as "gMsgCount".

Any idea why I am getting the above error? If I remove the reference to DTSGlobalVariables("gMsgCount").Value in the MsgBox statement the script executes without the error, but obviously I do not get a count because the global variable is not being referenced.

--
Message posted via http://www.sqlmonster.com