dbTalk Databases Forums  

SSIS:: Pass Parameters to Oracle in DataFlow task

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.dts SSIS:: Pass Parameters to Oracle in DataFlow task

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts


Discuss SSIS:: Pass Parameters to Oracle in DataFlow task in the microsoft.public.sqlserver.dts forum.



Reply
Page 2 of 2 < 1 2
 
Thread Tools Display Modes
  #11  
Old   
msnews.microsoft.com
 
Posts: n/a

Default Re: SSIS:: Pass Parameters to Oracle in DataFlow task - 10-21-2008 , 03:30 AM





Ohh....got it. Thanks Gerald.

"Gerald Aichholzer" <gerald.aichholzer (AT) gmx (DOT) net> wrote

Quote:
Hello Abba,

msnews.microsoft.com wrote:
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?


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



Reply With Quote
  #12  
Old   
msnews.microsoft.com
 
Posts: n/a

Default Re: SSIS:: Pass Parameters to Oracle in DataFlow task - 10-21-2008 , 03:30 AM





Ohh....got it. Thanks Gerald.

"Gerald Aichholzer" <gerald.aichholzer (AT) gmx (DOT) net> wrote

Quote:
Hello Abba,

msnews.microsoft.com wrote:
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?


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



Reply With Quote
  #13  
Old   
msnews.microsoft.com
 
Posts: n/a

Default Re: SSIS:: Pass Parameters to Oracle in DataFlow task - 10-21-2008 , 03:30 AM


Ohh....got it. Thanks Gerald.

"Gerald Aichholzer" <gerald.aichholzer (AT) gmx (DOT) net> wrote

Quote:
Hello Abba,

msnews.microsoft.com wrote:
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?


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



Reply With Quote
  #14  
Old   
msnews.microsoft.com
 
Posts: n/a

Default Re: SSIS:: Pass Parameters to Oracle in DataFlow task - 10-21-2008 , 03:30 AM


Ohh....got it. Thanks Gerald.

"Gerald Aichholzer" <gerald.aichholzer (AT) gmx (DOT) net> wrote

Quote:
Hello Abba,

msnews.microsoft.com wrote:
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?


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



Reply With Quote
  #15  
Old   
msnews.microsoft.com
 
Posts: n/a

Default Re: SSIS:: Pass Parameters to Oracle in DataFlow task - 10-21-2008 , 03:30 AM


Ohh....got it. Thanks Gerald.

"Gerald Aichholzer" <gerald.aichholzer (AT) gmx (DOT) net> wrote

Quote:
Hello Abba,

msnews.microsoft.com wrote:
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?


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



Reply With Quote
  #16  
Old   
msnews.microsoft.com
 
Posts: n/a

Default Re: SSIS:: Pass Parameters to Oracle in DataFlow task - 10-21-2008 , 03:30 AM


Ohh....got it. Thanks Gerald.

"Gerald Aichholzer" <gerald.aichholzer (AT) gmx (DOT) net> wrote

Quote:
Hello Abba,

msnews.microsoft.com wrote:
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?


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



Reply With Quote
  #17  
Old   
msnews.microsoft.com
 
Posts: n/a

Default Re: SSIS:: Pass Parameters to Oracle in DataFlow task - 10-21-2008 , 03:30 AM


Ohh....got it. Thanks Gerald.

"Gerald Aichholzer" <gerald.aichholzer (AT) gmx (DOT) net> wrote

Quote:
Hello Abba,

msnews.microsoft.com wrote:
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?


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



Reply With Quote
Reply
Page 2 of 2 < 1 2

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top