Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    6

    Unanswered: 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 Function

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    gtheo, 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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2004
    Posts
    6
    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!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Aug 2004
    Posts
    6
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •