I am very new to DTS, so please bear with me.

I have set up simple DTS packages using just Transform Data Tasks and
Execute SQL Tasks, and they work well. One of the data sources is an
Oracle 8i database.

Now I would like to select data from the Oracle database based on data
already in my SQL Server database (do an incremental update), but
appearently it is not possible to use parameters because the Oracle
OLE Provider does not support parameters. Is there another way to do
this?

E.g., is it possible to write an ActiveX Task to manipulate the SQL in
an existing Transform Data Task so that only literal "parameters" are
sent in the SQL to Oracle. Or is there a better, different way to do
this?

Even if someone could just point me in the right direction, it would
be a great help.

You can use a couple of methods

1. Filter the select statement that you use in the SourceSQLStatement by
changing it through an Active Script task.

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

You can use whatever method you want to populate those GVs but they would
come from reading your SQL Server destination table.

This will let you filter the results.

2. Otherwise you can use an Active Script transform to filter them as they
come through but this operated at the row level so may be slow.



--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Les Russell" <lrussell (AT) sugaraustralia (DOT) com.au> wrote

Thanks Allan.

Perfect response. I used your first method and after a bit of
fiddling with VB Script it runs like a dream. The article and sample
code by Darren Green was also invaluable.

Thanks again

OK changing the SQL using an ActiveX Script works but...

Doesn't any other driver to access oracle support parameters??
I've tried using Microsoft ODBC for Oracle but couldn't make it work.

Les Russell wrote:

I am not sure. From what I see, No

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Ricardo Sada" <rsada (AT) solcominter (DOT) com> wrote