SQL Server Performance Forum – Threads Archive
Tips on executing SQL on Oracle with DTS ?Hello, I am in the process of migrating SQL Server tables to Oracle, but still intend to process data from SQL Server DTS 2000. Therefore, could anyone please give me some tips on the following, or point me to some articles which might help explain the following issues / requirements : – Execute multiple Oracle SQL statements from a single Execute SQL DTS Task
– Pass in global variables to an Oracle Execute SQL Task When previously executing SQL on SQL Server databases, I have been able to pass in the global-variables from InputGlobalVariableNames, set it to a local variable using "SET @my_variable = ?" and then reference that local variable throughout the T-SQL script, as per normal. This doesnt seem to be possible when executing SQL on an Oracle DB. I can pass the global-variable in OK, but its the storing it locally that Im having trouble with. Usually in Oracle, I could "SELECT ? my_variable FROM dual", but this comes back to not being able to execute multiple SQL statements from the one task. – Re-define SQL Server tables to Oracle format ( keeping data-types, etc as close as possible )
– Migrating existing data to Oracle ( fastest to export from SQL Server, and load using SQL*Loader ? instead of doing data-pumps which are very slow ? ) If anyone could give me some pointers on the above I would be very grateful, or just general tips on using DTS with Oracle data… Thanks,
Though this question hasn’t been answered, I wanted to bring it up again as I will soon need to be modifying existing DTS packages to load data from SQL Server to Oracle. I have not yet worked with Oracle, and if anyone has any tips, suggestions, or warnings in regard to using DTS with Oracle, it would be very helpful. Thanks,
Hi Rachel, ask for Oracle Migration Workbench installation. Even if that tool doesn’t do a perfect job (it didn’t a few years ago) you can learn a lot playing with it. Mirko.