September 12, 2018 at 2:38 pm
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 -
September 12, 2018 at 3:35 pm
Michael Gerholdt - Wednesday, September 12, 2018 2:38 PMIn 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::TermCodeThe 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 -
How about making that particular query, dynamic? Form the query expression based on the value of the variable?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 13, 2018 at 6:16 am
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
How about you replace it with a ? (Indicating its a bind variable)
where a.term_code_key = ?
And have the parameter defined set to :User::TermCode? would that work
September 13, 2018 at 10:04 am
Thanks, I got this sorted. Could not use the ? and set parameter because when you use an sql variable as the sql source that option isn't available.
But I did just move the logic from the bit that was setting the variable into each of the main queries, and that worked. Which I believe is what Steve suggested.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply