Sql-server – SQL Server Migration Assistant for Oracle

This is the sort of thing that DTS/SSIS is commonly used for. One major warning, though, is that the out-of-box components for loading to an Oracle database are unacceptably slow for row counts over about 1000 (less if you need really fast loads). If I were doing this on a regular basis (every hour or so, for example), I'd probably use SSIS to create a package, but my load to Oracle would be through SQL*Loader (it's a ton faster). As for the data integrity piece, it's not too difficult to use SSIS to do the verification of the data, assuming you have relatively straightforward foreign key relationships. If you have extremely complex integrity constraints, you'll be better off creating a stored proc on the destination DB that you call with the SQL Server data after loading it into a staging table (this is the approach that Oracle EBS uses, so it's almost got to be a best practice by definition).

In SQL Management Studio:

• Click on the jobs folder in Object Explorer
• Display the Object Explorer Details window (under View menu)
• Select all or some of your jobs
• Right-click, script job as Create To New Query Window
• Change the connection of your query window to the new server
• Execute the generated script(s)

There you go!

Note: this is for SQL 2005+. SQL 2000 Enterprise Manager has an option to Generate Scripts on the Job Agent, I believe.