DTS: migrating many, multiple, new databases regularly

I am looking for some advice on best ways to use DTS between our various test database servers.

I need to test an application against MS SQL Server 2000/2005 and Oracle 9i/10g. This includes creating new databases,logins etc on each server. And also means I need to recreate all my test data again at least twice; on Oracle and MS SQL Server(i.e I can backup and restore my databases between MS SQL and Oracle versions). I am looking to for a better way to achieve this and am here looking for some advice on the best way.

DTS (tried only on MS SQL 2000 so far) does seem to help however, one problem I have come across is mapping tables. The application creates little over 200 tables, although the schema remains over all remains identical, but with slight differences. E.g different name formats. For instance. a table on SQL2000 is called "Table" whereas Oracle calls is "TABLE". This means, on the DTS wizard I need to drop down and select the correct table name for each table.

What could be other possible and efficient ways to achieve this? Also, I need to do this for around 10 multiple databases and not just one database. I will also need to be doing this frequently, if not regularly as I will be creating new test databases and test data.

Any inputs will be appreciated. Thank you.

i think DTS or SSIS of sql server 2005 (the true ETL Tool with much more advanced features than DTS) can handle this problem. If you want to create your own automated script then you can configure LinkedServer from your staging server and fetch the data.

Madhu

|||Thanks for the reply Madhu.

I am quite inexperienced and still learning a lot of things. I have only used DTS a few times. SSIS? Havent really heard of it before!
If I was to use DTS to transfer data from my MS SQL200 database to Oracle 9i/10g and SQL2005, what would be an easy way to map these 200 tables rather than going and selecting the correct table from destination database?

Thanks once again.|||

if DTS is the ETL tool of SQL Server 2000 , SSIS is enhanced , Feature rich ETL tool in SQL Server 2005. Here only name is not changed, there are lot of enhancement as far as the features are concerned. If you wnat to transfer the data from SQL Server 2000 to 2005 then you must create SSIS not DTS. I am not sure about transporing to oracle. But you can configure Linkedserver to oracle/sql server and you can create synonyms in sql server 2005 which will be as good as you are accessing the object from your own database.

Madhu

|||

N

There is alot information available on the SQL Server books online about SSIS,make sure you have downloaded the latest update from Microsoft.

Look at this blog http://sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx that helps.

http://www.developer.com/db/article.php/3635316

http://msdn2.microsoft.com/en-us/sql/aa336312.aspx

|||Thanks again Madhu.
This is useful. However, just as I mentioned in my previous post
-I purely need to copy the test data from my 200 tables that the application has created to or fro Oracle server/SQL Server; as the requirement is for
-copying many such databases and not just one database
-the application should be able to run independently on each server. as we not not bother about the server, we need a solution so that we do not need to create test data twice on each database SQL and Oracle databases.
-this is going to again be regular thing that I should be able to do as I will need to create new type of test data move it to other type of server (may it be Oracle or SQL) and test the application on both these databases.
Secondly, I have tried DTS - and what I need is a way to be able to map the tables. The wizard does not recognize the same table names with different cases. like A_Table and A_TABLE(oracle's table)|||Satya, Thanks for the links. They look very useful.