Professional SQL Server™ 2005 Integration Services

18.3. Code Deployment and Promotion from Development to Test to Production

In DTS, you had the option to store packages in SQL Server or persist them to disk as either a Visual Basic or Structured Storage file. But most DTS development occurred within the context of a SQL Server.

SQL Server Integration Services is decoupled from the SQL Server engine. Packages are developed in either Business Intelligence Development Studio or Visual Studio 2005. Because of this, code promotion is addressed in different ways.

For instance, in DTS you would likely develop a package on a local or development server. You would unit test the package to ensure proper functionality and desired results, and then you would click Package Save As to promote the package to a test server. This was by no means the only method available, but it was a popular method for accomplishing package migration through the SDLC hierarchy in many SQL Server 2000 shops.

Now that SSIS is decoupled from the SQL Server environment, developing packages is equivalent to external software development. Some DBAs have experience as developers. To them, this will pose no challenge or threat. To others, this may be outside their comfort zone. To the uncomfortable, I say, "Relax. You can do this!"

18.3.1. The Deployment Wizard

You will now look at one method for migrating a package created in Visual Studio 2005 into an instance of ...