Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Copying objects between DBs in SQL Server 2005

Status
Not open for further replies.

mellenburg

Programmer
Aug 27, 2001
77
US
We're moving from SQL Server 2000 to 2005. In 2000, I had a development, test, and production database. I had written a variety of DTS packages to move select objects from development to test, and from test to production. I wasn't doing an entire DB copy because I wanted to be able to move specific objects.

In 2005, I understand that DTS has been replaced by SSIS. So, I now want to replicate that exact functionality on the 2005 server. I don't want to use DTS anymore.

How do I create an SSIS package that will allow me to select specific database objects (tables, views, stored procedures) and copy them from one 2005 DB to another 2005 DB? Furthermore, I would like the solution to reside on the DB server like the DTS packages did so that multiple users can execute the package.
 
Mellenburg,

RiverGuy is right, there is a preformed task in SSIS to transfer objects from one db to another, and there are even tasks to "upgrade" DTS packages to SSIS packages. And...not to pick on MS too much...because these tasks do work--IF you want to transfer all the DB objects, and IF the DTS package is VERY simple.

Not to discourage you (not my intent) but SSIS usage has a very STEEP learning curve, especially if you're familiar with DTS. There are countless posts here and in the MSDN and TechNet forums where the general consensus is having experience in DTS is actually a hinderance to learning SSIS. That said...SSIS is much more powerful and even useful, once you've acquired the knowledge and experience to be able to use it.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top