SSIS:: Pass Parameters to Oracle in DataFlow task

[msnews.microsoft.com]

Hello,

Iam trying to execute the below query against Oracle OLEDB Source in a data
flow task:

select frq_code, frq_name, update_frq, uptime_frq
from frequency_bcs
where update_frq > ?
and update_frq <= ?

and its giving me the error:

"Parameters cannot be extracted from the SQL command. The provider might
not help to parse parameter information from the command. In that case, use
the "SQL command from variable" access mode, in which the entire SQL command
is stored in a variable. "

As suggested, I chose "SQL command from variable", created a variable and
stored the entire above string in it. Now, how can I pass the two parameters
to the query, as I do not see the 'parameters' button?


Thank you,
Abba

[Gerald Aichholzer]

Hello Abba,

Post by msnews.microsoft.com
Iam trying to execute the below query against Oracle OLEDB Source in a data
select frq_code, frq_name, update_frq, uptime_frq
from frequency_bcs
where update_frq > ?
and update_frq <= ?
"Parameters cannot be extracted from the SQL command. The provider might
not help to parse parameter information from the command. In that case, use
the "SQL command from variable" access mode, in which the entire SQL command
is stored in a variable. "
As suggested, I chose "SQL command from variable", created a variable and
stored the entire above string in it. Now, how can I pass the two parameters
to the query, as I do not see the 'parameters' button?

if you're working with "SQL command from variable" you will have to
build the sql string with parameters resolved (like dynamic sql).
You could declare a variable having EvaluateAsExpression=true and
an expression like

"select ... from ... where criteria1 = " + @[User::Criteria1] +
" and criteria2 = " + @[User::Criteria2]

where Criteria1 and Criteria2 are two additional variables (in the
namespace User, which is the default namespace) matching your current
criterias. Of course you'll have to take care about proper quoting
if these are strings.

hth,
Gerald

[msnews.microsoft.com]

Ohh....got it. Thanks Gerald.

Post by Gerald Aichholzer
Hello Abba,

Post by msnews.microsoft.com
Iam trying to execute the below query against Oracle OLEDB Source in a
select frq_code, frq_name, update_frq, uptime_frq
from frequency_bcs
where update_frq > ?
and update_frq <= ?
"Parameters cannot be extracted from the SQL command. The provider might
not help to parse parameter information from the command. In that case,
use the "SQL command from variable" access mode, in which the entire SQL
command is stored in a variable. "
As suggested, I chose "SQL command from variable", created a variable and
stored the entire above string in it. Now, how can I pass the two
parameters to the query, as I do not see the 'parameters' button?

if you're working with "SQL command from variable" you will have to
build the sql string with parameters resolved (like dynamic sql).
You could declare a variable having EvaluateAsExpression=true and
an expression like
where Criteria1 and Criteria2 are two additional variables (in the
namespace User, which is the default namespace) matching your current
criterias. Of course you'll have to take care about proper quoting
if these are strings.
hth,
Gerald