Hi,
I have a source-Oracle-database in which I want to retrieve data and store
those data in a SQL-database.
Which data need to be retrieved from the oracle database is stored in the
destination-sql-database. How can I indicate this in the source-query?
I'm using SQL2005 and I'm pretty new to DTS-packages.
Thank you for the information.

If you are using SQL 2005, can we assume you are using SSIS, not DTS?

If so, you will need some way to retrieve the values from the SQL database
BEFORE you run the Oracle pull.

You could use an Execute SQL task on the Control Flow, and set the ResultSet
to Single Row, then map the result set fields to package variables.

Then in the Oracle Source Adapeter, make use of those variables in a
parameterized SQL statement.

This is all pretty high-level stuff. Post back a reply if you need help with
any specific task.

=====
Todd C


"amac" wrote:

Hi Todd,
Thank you very much. I'm getting pretty far, but I can't find how to use a
parameter in the query. Can yo tell me?

"Todd C" wrote:

With SQL based Source connections, it is usually as simple as a ? As in:

SELECT [a], [b]...
FROM dbo.[xyz]
WHERE [c] = ?

(Hint: Make sure you 'seed' the variable with a typical value so that during
design time, the parser can make heads or tails out of it.)

With other system, like IBM AS 400, or other legacy connections, you may
have to build the SQL string inside a script task;

Dts.Variables("MySQL").Value = "SELECT...FROM...WHERE [c] = " +
Dts.Variables("MyParameter").Value.ToString

Watch the data types. If MyParameter is a string, then you will need to
include single quotes on either side of it.

Then in the Connection Manager, set the source as SQL from Variable and pick
the MySQL variable. Again, make sure you 'seed' a valid SELECT statement in
the Variable value so that the designer can make sense of it.

Keep us posted.
=====
Todd C


"amac" wrote:

Todd, Thanks again, but unfortuanatilly, it doesn't work. I tried a couple of
things:
select * from XAL_310.DEBTABLE Where DataSet=?[User:ataSetToImport]
select * from XAL_310.DEBTABLE Where DataSet=@[User:ataSetToImport]
select * from XAL_310.DEBTABLE Where DataSet=?
select * from XAL_310.DEBTABLE Where DataSet=?DataSetToImport
Nothing works.

DataSetToImport is the parameter I retrieved in a previous step and it is
filled the correct way (checked it in debug).
XAL_310.DebTable is a table in an oracle-database (connection via .net
Providers).
Do you have any other suggestion?
"Todd C" wrote: