Help - VB Script in DTS, if not working.

  • Hi,

    The code below is what I'm trying to achieve but it comes back with an error. I could do the same with a case statement but I would prefer to use some VB script and transform the results that way.

    What am I doing wrong???

    Cheers,

    Chris

    Function Main()

    If DTSSource("Q1") = "1" then DTSDestination("HDA_Attitude") = "Poor" else

    If DTSSource("Q1") = "2" then DTSDestination("HDA_Attitude") = "Satisfactory" else

    If DTSSource("Q1") = "3" then DTSDestination("HDA_Attitude") = "Good" else

    If DTSSource("Q1") = "4" then DTSDestination("HDA_Attitude") = "Very Good" else

    DTSDestination("HDA_Attitude") = "Excellent"

    End If

    Main = DTSTransformStat_OK

    End Function

  • Your code does not pass the parse

    Try this one

    Function Main()

    If DTSSource("Q1") = "1" then

    DTSDestination("HDA_Attitude") = "Poor"

    elseIf DTSSource("Q1") = "2" then

    DTSDestination("HDA_Attitude") = "Satisfactory"

    elseIf DTSSource("Q1") = "3" then

    DTSDestination("HDA_Attitude") = "Good"

    elseIf DTSSource("Q1") = "4" then

    DTSDestination("HDA_Attitude") = "Very Good"

    else

    DTSDestination("HDA_Attitude") = "Excellent"

    End If

    Main = DTSTransformStat_OK

    End Function

    Tom

  • Hi Tom,

    That code worked great. Just comparing them the only difference I can see is elseif in your code and the else if in my code. I find it strange that it will not allow that syntax in the elseif parts but will see them seperatley in other parts.

    Anyhow thanks again, much appreciated.

    Cheers,

    Chris

  • You may also want to consider using the vbscript based SELECT CASE statement as it is a bit cleaner and less code involved but produces the same results.

    Function Main()

    SELECT CASE DTSSource("Q1")

    CASE "1"

    DTSDestination("HDA_Attitude") = "Poor"

    CASE "2"

    DTSDestination("HDA_Attitude") = "Satisfactory"

    CASE "3"

    DTSDestination("HDA_Attitude") = "Good"

    CASE "4"

    DTSDestination("HDA_Attitude") = "Very Good"

    CASE ELSE

    DTSDestination("HDA_Attitude") = "Excellent"

    END SELECT

    Main = DTSTransformStat_OK

    End Function

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

    Edited by - antares686 on 06/25/2002 5:44:42 PM

  • Cheers for your suggestion, to get around this prior to my question I'm using the following SQL Query...

    select

    Q1 = case

    when q1 = 1 then 'Poor'

    when q1 = 2 then 'Satisfactory'

    when q1 = 3 then 'Good'

    when q1 = 4 then 'Very Good'

    when q1 = 5 then 'Excellent' end

    from dbo.customer_survey_main

    Any thoughts on what would be the better performer??

    Cheers,

    Chris

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply