DTS - Oracle Schema name

Hi

I have created a very simple DTS. The DTS is used to trasfer data from Oracle to MSSQL

Eg: Select empno, empname from schemaname.employee where isactive ='Y'

I want to transfer the above data from oracle to MSSQL. I am able to create the DTS and run the DTS successfully.

Right now the schema name is harcoded. But in real scenario, the schema name is known during run time. I want to pass the schema name during run time. How to do this?

I am using Transform Data task to transfer data from oracle to mssql.

any idea?

JtamilI have never done this, but have a look at the dynamic properties task. You may be able to declare the table as a variable of some sort.|||MCrowley is right...use a dynamic variable

in your dts, right click and add a dynamic properties task
then after you declare the variable just supply it to the connections

I use dynamic variables to supply my database name
You maynot need this, but I call my dts through a bat file and i supply the values:
dtsrun /S<database> /U<user> /P<password> /N<dts name> /A sourceDB:8=cpt4

sourceDB is my dynamic variable and 'cpt4' is the value (used to point to the correct database)

When you look at the connections, one should be for your sql server data and one should be your oracle connection. Just put the created variable as the value. Then when you run it just make sure you supply the value and everything should work