Friday, March 9, 2012

DTS Active X transform script error

I can't for the life of me figure out why I am getting an error with this simple script. The error I get is "expected end" on line "22" which is the first "elseif" in the nested if statement. This makes no sense as obviously I don't want an end if statement there. Any ideas where my syntax is wrong would be greatly appreciated.

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()

dim txncode
dim cashin

txncode = Trim(DTSSource("Col004"))
Cashin = Trim(DTSSource("Col005"))

if txncode = "" or IsNull(txncode) then
Main = DTSTransformStat_SkipRow
exit Function
end if

select case txncode
case "210"
if (Cashin > "0" and Cashin < "1000") then txncode = "210-1000"
'msgbox txncode
elseif ("1000" < Cashin and Cashin < "5000") then txncode = "210-1000-5000"
elseif (Cashin > "5000") then txncode = "210-5000"
else txncode = "210"
end if
msgbox txncode
Main = DTSTransformStat_OK
exit function

case else
Main = DTSTransformStat_OK
exit function

end select

DTSDestination("TransactionCode") = txncode
Main = DTSTransformStat_OK
End Functiongtheo, I hope someone is able to answer your question, but most of the heavy posters on this forum long ago stopped putting logic into DTS. Use it to load staging tables and the put your transform logic into a sproc.|||Problem Solved - it was twofold.

1) inline if-then made it expect it to have an end, if using multiple else you need then on next line.
2) quotes around numbers made them evaluate as text.

sproc solution untenable as that would entail schema changes, a no-no in a big bad corporate world development environment where I am on the client side, not dev!|||Glad you found the solution, but...

huh?

Why would a sproc solution imply schema changes? Are you talking about the staging tables? If necessary, those can be maintained in a separate database on the same server. As a consultant, I dip into a dozen or more big bad corporate worlds every year and there is always a way to implement this.|||The problem is an immature product that means a fat client (including full SQL Client) and no separation between an "application service user" and the end user. So the end user has to execute the DTS - meaning they have to create any temp tables, etc that are used; also I'd be discouraged from creating a separate database if not forbidden by the bank clients. They don't want anything that is not an "offical release". So, adding objects is not advised, and a separate database would be thrown back at me. So the easiest thing to do is have a structured storage file DTS Package that sits on the client and executes the logic inside itself. This is not viewed as custom code per se. Makes no sense to me but it's the way I get around them.|||Foolish people. Make sure then have your contact information so they can easily get in touch with you to rewrite the whole thing when they upgrade to 2005.

No comments:

Post a Comment