October 15, 2008 at 11:32 am
What would be the best way to transfer data from one database to another using SSIS, both the databases are on the same server. I need to transfer most of the tables in the source database. Is there a way I can avoid having to write stored procedures to do the transfer for each table within the source database.
October 15, 2008 at 11:37 am
If this is a one time thing, I suggest using the Import/Export Wizard in SSMS. Right-click on either the source or destination database, select Tasks, and select either Import data... (if you clicked on the destination db) or Export data... (if you clicked on the source db).
Greg
October 15, 2008 at 1:06 pm
No, this is not a one time thing. It needs to be a scheduled task.
October 15, 2008 at 1:16 pm
MS provides a control flow object "Transfer SQL Server Objects Task" to accomplish exactly what you are trying to do I do recommend in general in these situations that you drop the recreate the object first so the first and second do not get out of sequence unless there are supposed to be sturctural differences.
On other consideration is the tables are very large is dropping the indexes prior to the transfer and then recreating them afterward.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply