DTS and ActiveX VB Script

gscott · April 17th, 2007, 02:06 PM

Hi Everyone -
I am having a little problem and I think I can thank Microsoft for it. I am trying to create a DTS package that runs a select statement that is a count of rounds and based on the count I want to run one set of code or another set of code.
How would you go about this?
What I have tried is a SQL statement with a global variable and then I am trying to reference the global variable in an activex script task - and the task is failing -
Task code is:

' Visual Basic ActiveX Script
Function Main()
    dim sql
    set sql = DTSGlobalVariables("CountofRec").Value
    If sql > 700 Then <--I get a type mismatch on this line
    Main = DTSTaskExecResult_Success
    Else
    Main = DTSTaskExecResult_Failure
    End If
End Function

What is wrong with this? Please help! As I would so grateful for it:)
Lost
Gina

happygv · April 18th, 2007, 05:42 AM

Try this...

Code:

'   Visual Basic ActiveX Script
Function Main()
    dim sql 
    sql = DTSGlobalVariables("CountofRec").Value
    MsgBox sql
    'If sql > 700  Then  <--I get a type mismatch on this line 
        'Main = DTSTaskExecResult_Success 
    'Else
        'Main = DTSTaskExecResult_Failure
    'End If
End Function

And see what it returns.

_________________________
- Vijay G
Strive for Perfection

gscott · April 19th, 2007, 08:45 AM

I get object required.
Error Code: 0
VBscript runtime error
Object Required 'DTSGlobalVariables(...)Values.'
Error on Line 5

Wait! wait! - I rechecked my GLobal Variable declarations and fixed something there and now I get the error
Error Code: 0
VBScript runtime error
Type Mismatch

happygv · April 19th, 2007, 09:04 AM

So what is the fix you did with Global variable declaration?

Basically it looks like the following line doesn't return an integer value. It returns some object type, which you can't check against 700

Code:

sql = DTSGlobalVariables("CountofRec").Value

_________________________
- Vijay G
Strive for Perfection

gscott · April 19th, 2007, 09:31 AM

I declared an output on a sql select statement that populates the countofrec variable. (Parameters and output parameters)
It is a select statement that counts the records and I want to compare that number and if it is higher then 700 do the rest of the dts package and if not just end.

happygv · April 19th, 2007, 10:51 AM

You can also refer this page - Using ActiveX Scripts in DTS

_________________________
- Vijay G
Strive for Perfection

jilly · May 16th, 2007, 07:38 AM

On the pane on the left (two tabs, Language and Browser) you can double-click on the available global variables and it will paste the correct reference into your code.

Turn on just-in-time debugging and step through your code to make sure you're referencing the variable correctly. (Right-click "Data Transformation Services" in Enterprise Manager and there's an option to turn on Debugging.)

Then within your code place a STOP statement and it will break during execution. SAVE your package before debugging. The debugger will always (?) close Enterprise Manager after you're done debugging.

Nothing in your code off-hand looks odd. Your options to do what you want are to add an ActiveX script within the workflow properties, or to, as you're trying to do, use a pass/fail condition to branch.

SQLDTS.com has additional information that you may find helpful.

--Jeff