DTS Import and Index on multiple tables

Posted on 2006-04-12
Last Modified: 2013-11-30
I've got a bunch of tables I am transferring from 2 Oracle databases to Microsoft SQL Server.  I opened the SqlServer Import/Export wizard and selected 40 tables from one source, and ran it, and it worked great, but when I tried to edit the package I found it had given me 40 completely separate data connections and transformations.  Then I set up Indexes on the tables, and, of course, the next morning, as it recreated the tables, it blew all of my indexes away.

I think need to set it up from scratch, using only one Source and one Destination, and preferrably only one script that runs all of my SQL code, but as an Oracle guy, DTS is new to me, and the import/export wizard doesn't create what I want.  If I could get some assistance getting started in the right direction, I think I can figure it out.  Also I'm new to SQLServer concepts in general, so I'm grateful for any level of detail you can offer along the way.

1. I'd like to create a general connection to my first Oracle database and rely on the SQL statements to get the tables I want.
2. I'll need to drop my indexes on the SQL Server side.
3. I wondered if there were any cool way to gather only the latest data that had changed from Oracle, I'll probably end up dropping my SQL Server tables and recreating them.
4. Then I'll run the 40 statements that gather the data from one Oracle, but I'll need to modify the SQL to name the tables uniquely on the SQLServer side, since I have another Oracle database with the exact same tables, and I want to import them into the same SQLServer database.  And how cool would it be if I could put all of the statements in a single script?!
5. Then I'll want to create the indexes
6. And I've heard rumors that I could have it email me when it is complete, and I'd like to see if I can make that happen.
7. And finally I'll need to do the same thing with my second Oracle database.

I opened the Enterprise Manager and messed around to see if I could intuitively create what I want, but it's not as intuitive as I hoped, so I'm leaving this request for help on Experts Exchange while I go start my education with tutorials on the web.

Thanks in advance, for your help!
Question by:AptDev
    LVL 30

    Accepted Solution

    Create a new package..

    1. Drop an ODBC or OLE DB connection on the page that connects to Oracle. Each data pump that goes from Oracle to SQL Server can use a SQL statement as its source.

    2. Drop a SQL server connection on to the page and confugire it. Now you can drop an Execute SQLTask on the page, set it to use the SQL Server connection, and run a DROP INDEX command. You use workflow to ensure it runs first.

    3. Assuming there is some kind of date stamp on the oracle table to identify new records, I usually use parameters in my source SQL to only gather data thats newer than say seven days old. Then I delete that same data in the destination tables before loading it.

    4. Can you add a column to your destination table which partitions the data. That way it all goes into the same table.

    5. Use an Execute SQL task as described in 2 (but don't add another SQLconnection - you can use the same one)

    6. Yep. You either need to use native SQL email functionality which is a bit of pain (need to install outlook client on the SQL Server) or you can use xp_smtpmail from - very handy

    7. You'll probably need two Oracle connections on your DTS.

    Finally ,this site is REALLY helpful with DTS


    Author Comment

    On number 3, when you are transferring only the data from the most recent 2 days, do you use a "select into" in an Execute SQL Task or do you use a Data Transformation Task?  If I use an Execute SQL task, and I can get a single query working, then I should be able to put a couple of dozen data transfers in a single script.  But if I use a Data Transformation Task, then I'll end up with 40 tasks running from my source to my destination database, is that true?
    LVL 30

    Expert Comment

    Yep you can probably use a select into, you'd need to set up a linked server to your Oracle database

    Linked servers are under 'Security' in Enterprise Manager, or you could look up sp_addlikedserver in books online

    Once you've set up your linked server you could do this:

    DELETE LocalTable
    WHERE DateField >= DATEADD(d,-7,GETDATE())

    INSERT INTO LocalTable (C1,C2,C3,C4,DateField)
    SELECT C1,C2,C3,C4, DateField
    FROM OracleDB.Database.Schema.Table
    WHERE DateField >= DATEADD(d,-7,GETDATE())

    I personally avoid using DTS as it can easily get its knickers in a know if fields disappear etc.

    If you were to use the script above you could put it in a stored procedure and just run that.
    LVL 30

    Expert Comment

    That stored procedure could also contain your index manipulation scripts as well as your emailing script.

    Author Comment

    thanks you nmcdermaid, it worked great!

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now