Copying objects between DBs in SQL Server 2005

[mellenburg]

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.

 

[RiverGuy]

There is a "Transfer SQL Server Objects" task in SSIS.

 

[SQLHacker]

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.