I am using SQL server 2008. I have a requirement to transfer data from a one database to another database tables which are in the same server. But the login credentials for each of the database is different. This task need to be done monthly basis. Which is the best approach? SQL job or DTS package? Please suggest any good article for this problem.

1 solution

Solution 1

1.Use SQL server Export Import wizard. There you will have facility to connect different server with different userid, password.
2.At the last window of the package you will have a provision to save your works as a SSIS package(DTS is no longer available from SQL 2005).
3.Schedule the the SSIS package to execute monthly basis using SQL Agent Job.