Chapter 3: Working with DTS Packages

Overview

In this chapter, you will learn how to:

• Save packages to SQL Server, Meta Data Services, structured storage files, and Microsoft Visual Basic files, and utilize version control

• Secure packages in Microsoft SQL Server and in the file system

• Execute packages using DTS Designer, SQL Server Enterprise Manager, a scheduled SQL Server Agent job, the DTSRunUI graphical utility, the DTSRun command prompt utility, and the Execute method

• Enable package execution logging and error logging

Before you add functionality to your DTS packages and continue building your data movement application, you need to learn how to work with these packages. Packages can be saved in several different formats, most of which utilize a form of version control. Each format has functionality and security advantages and disadvantages. You need to understand the differences between the formats before you build the rest of your data movement application, so you will work with each in this chapter.

You also need to ensure that the packages you create are secure ”DTS packages can perform a variety of powerful tasks , but only authorized users should be able to view, modify, or execute these packages. The packages that you created in Chapter 1 and Chapter 2 have been created as secure packages. However, you need to consider additional security and backup issues before you continue developing your data movement application.

Finally, understanding how packages are executed is important to the design of your packages. In this chapter, you will learn how to pass global variable values at run time to control the execution of a package. You will also learn how to enable execution logging and error logging to assist in package debugging and auditing.