ActiveX Script in DTS not working like expected

[Joe]

I am modifying a DTS package that was built a few years back and
trying to add a simple transform using ActiveX. The ActiveX code that
I have is,

dim varFilename, varCardType
varFilename = DTSGlobalVariables("gv_ExcelLink").Value
'Remove file extension
varFilename = LEFT(varFilename,(LEN(varFilename)-4))
If RIGHT(varFilename,4) = "VISA" Then
varCardType = "Visa"
Else
varCardType = "Mastercard"
End If

Msgbox(varCardType)

'DTSDestination("CardType") = varCardType

I am trying to access the filename of the Source (which is held in a
GV) and depending on the filename of the source, insert either "Visa"
or "Mastercard". This code is returning the following error message,

"Wrong number of arguments or invalid property assignment."

The offending line seems to be, varFilename = DTSGlobalVariables
("gv_ExcelLink").Value, but if I preface this line with Set, then the
error goes away.

How can I accomplish this?

Thanks,
Drew

[tbradshaw via SQLMonster.com]

Hello Drew,

Although nothing jumps out as being obviously wrong, here are some gotcha's
that I will share with you.

(1) Use "Option Explicit". Above Function Main(), put "Option Explicit" on
its own line. That way it'll flag all variables you haven't DIM'd, like
misspellings.

(2) Make sure all global variables TRULY exist. Case matters here. For
example:
"gv_ExcelLink" and "gv_excelLink" are two different variables, although you
will bang your head against a wall for a good part of the day figuring that
out. Save you head.

My gut feeling is that
DTSGlobalVariables("gv_ExcelLink").Value
is a spelling/case variation on what's really out there. In short, you're
trying to assign a NULL value.

Also, try putting more MsgBox's thru the code. Maybe that'll help you
pinpoint the problem.

Let us know how you make out.

Best Regards,
Tom Bradshaw

Thomas Bradshaw
Data Integration Services
MyWebGrocer LLC

[Joe]

Post by tbradshaw via SQLMonster.com
Hello Drew,
Although nothing jumps out as being obviously wrong, here are some gotcha's
that I will share with you.
(1) Use "Option Explicit".  Above Function Main(), put "Option Explicit" on
its own line.  That way it'll flag all variables you haven't DIM'd, like
misspellings.
(2) Make sure all global variables TRULY exist.  Case matters here.  For
"gv_ExcelLink" and "gv_excelLink" are two different variables, although you
will bang your head against a wall for a good part of the day figuring that
out.  Save you head.
My gut feeling is that
DTSGlobalVariables("gv_ExcelLink").Value
is a spelling/case variation on what's really out there.  In short, you're
trying to assign a NULL value.
Also, try putting more MsgBox's thru the code.  Maybe that'll help you
pinpoint the problem.
Let us know how you make out.
Best Regards,
Tom Bradshaw
Thomas Bradshaw
Data Integration Services
MyWebGrocer LLC
--
Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-dts/200903/1

Thanks Tom! I appreciate your reply! I have messed with it all
morning, and finally decided to work around it by using a different
GV... the GV I was using was correctly spelled, heck I was putting it
in the script by double-clicking it in the Browser so that I could
make sure. It just didn't like that variable at all.

Here is the code I used,

Option Explicit
Function Main()
dim varCardholder, varCardType
varCardholder = DTSGlobalVariables("gv_CardholderID").Value
If RIGHT(varCardholder,4) = "VISA" Then
varCardType = "VISA"
Else
varCardType = "Mastercard"
End If

DTSDestination("CardType") = varCardType

Main = DTSTransformStat_OK
End Function

Thanks!
Drew