Inconsistent experience using SSIS varables with parameter as Oracle bind variables

  • In the self-same SSIS package, I have two data flow tasks. Each connects to the same Oracle server to return data. For each of them, I created a variable that holds the SQL statement. Also, in each of them the SQL Query references a parameter that is populated by another SQL Query which runs first.

    Basically, I'm hitting the Oracle database to obtain a current term code, which value gets assigned to a parameter. Then I use that parameter in each of the variables' SQL statement n order to pull data pertinent to that term code.

    One of the variable queries utilizes the SSIS user parameter in the table join area of the sql query. This one works just fine. It looks like:
    and b.term_code_key = :User::TermCode

    The other utilizes the SSIS user parameter in the where clause. This one returns ORA-01745: invalid host/bind variable name. It looks like:
        where a.term_code_key = :User::TermCode

    ( the difference in table alias is correct, btw, so that's not relevant)
    Regarding data type, it's a string.

    I can return data and push it to the destination if I disable the second query, but when both are enabled the error rules the day and nothing happens.
    If I hard code the term code into the variable SQL statement rather than using the user parameter, it works just fine, so the rest of the query is healthy.

    Any ideas? Thanks -

  • oops, wrong forum, sorry.

Viewing 2 posts - 1 through 1 (of 1 total)

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