Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    41

    Thumbs down Unanswered: 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?

    Jtamil

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I 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.

  3. #3
    Join Date
    Apr 2003
    Posts
    15
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •