[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


DTS Import and Index on multiple tables

Posted on 2006-04-12
Medium Priority
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
  • 3
  • 2
LVL 30

Accepted Solution

nmcdermaid earned 2000 total points
ID: 16441457
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  www.sqldev.net - very handy

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

Finally ,this site is REALLY helpful with DTS


Author Comment

ID: 16450087
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

ID: 16452477
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

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

Author Comment

ID: 16738488
thanks you nmcdermaid, it worked great!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

834 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