Post reply

Strategy Needed

  • May 14, 2004 at 8:33 am

    #82892

    Hello experts. 

    BACKGROUND: I work for a satellite campus for a state university which has a centralized data warehouse (Oracle).  The particular college for which I work wants to track students progress through their graduate program, as well as demographics for their periodic accreditation reviews.  They want to keep this data in a SQL Server.

    PROBLEM: I need a strategy for getting data from many tables in the Oracle warehouse into tables in a SQL Server.  The tables are not identical (i.e. the Oracle warehouse has an entire table for student email addresses and we would like to keep email addresses as a field in a "studentInformation" table) and so a standard DTS package does not seem to work.  I can also try to create a text file using a DTS package with an "execute SQL command" and ActiveX script and then try to import from these text files into our tables but that seem inefficient.  I need a strategy please

     

    Thanks in advance,

    Seth

    Kindest Regards,

    Seth Washeck

    If nothing in this world satisfies me, perhaps it is because I was made for another world.
    C. S. Lewis

  • May 14, 2004 at 9:34 am

    #506509

    Seth,

    You might find the data easier to work with if you use DTS to import the tables unchanged from Oracle into a SQL Server 'staging' database then transform the data as needed into your final SQL Server database.  I used this strategy last year to transfer 6 Oracle tablespaces in a single database to 6 separate SQL Server databases.

    BTW, the example you give (taking columns from several Oracle tables and putting them in a single SQL Server table) can be accomplished in DTS by joining the source tables with an SQL query in a Transform Data task.

     

    Greg

    Greg

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply