July 26, 2004 at 10:46 am
I have a 11GB Oracle DB. I would like to export the data to a MSSQL DB. I was able to do a DTS using the GUI interface and was able to import the data. But now I would like to crete a Local Package and also create DDLS before I import the data. What I would like this package to do is as follows
1. Create a coonection to ORacle and SQL server
2. Create DDLs to create tableson SQL Server
3. Export data from Oracle to SQL Server
4. Run the indexes SQL
5. Run foriegn key SQL
Can I just take the package and run it standalone that will create all these steps? Please let me know whether I can do these things?
Thanks
July 26, 2004 at 11:53 pm
Depending on the number of tables from your Oracle database, this can be really easy or a real chore.
1. At the end of the DTS GUI, you can save your package to SQL Server. Do this to create your starting DTS package.
2. SQL Server will list this SQL package (whatever name you gave it) in the Data Transformation Services folder under Local Packages.
3. When you double-click to open your package what you will see is one or more Transformation tasks (depending on the # of tables you imported).
4. Add an Execute SQL task (there is an icon on the left in the DTS toolbar when DTS comes up). Enter your DDL statements here. There is a dropdown to select the connection to use. SQL Server will have already created the appropriate connections to both SQL Server and Oracle).
5. Repeat step 4 for the indexes and foreign key creates
6. Now the fun part. Precedence constraints. You want the DTS task to run the DDL statements first, then do the import, then do the indexes and foreign keys. You can read on how to do this in BOL, but the real pain is that by default each of the transformation tasks are separate. Since you want the DDL to come first, you have to manually draw a precedence contraint from the DDL Execute SQL task to *each* transformation task. Same analogy for the indexes and keys.
You don't *have* to do item #6 if you are comfortable with having multiple packages. I tend to like to keep everything in a single package so I don't have other dependencies. This is something I'm sure you'll get opinions.
A great resource to try is http://www.sqldts.com.
July 27, 2004 at 12:17 pm
Thanks for your input! Appreciate it
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply