DTS Import and Index on multiple tables
Posted on 2006-04-12
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!