Retrieving Data based on parameters entered in Destination Databas

Discussion:

(too old to reply)

amac

2009-04-03 12:04:01 UTC

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.

Todd C

2009-04-03 12:47:01 UTC

Permalink

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

Post by amac
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.

amac

2009-04-03 13:23:04 UTC

Permalink

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?

Post by Todd C
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

Todd C

2009-04-03 14:50:04 UTC

Permalink

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

Post by amac
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?

amac

2009-04-07 07:57:01 UTC

Permalink

Todd, Thanks again, but unfortuanatilly, it doesn't work. I tried a couple of
things:
select * from XAL_310.DEBTABLE Where DataSet=?[User::DataSetToImport]
select * from XAL_310.DEBTABLE Where DataSet=@[User::DataSetToImport]
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?

Post by Todd C
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

Post by amac
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?