As a database administrator, one of the common tasks you face is transferring data between different data sources. In the past, this was a challenging and time-consuming process. However, with the introduction of Data Transformation Services (DTS) in SQL Server 7.0 and further improvements in SQL Server 2000, this process has become much easier and more efficient.

DTS is a technology that allows you to exchange and modify data among any OLE DB compliant data sources. It is implemented as a set of programmable objects, accessible through programming and scripting, as well as a graphical interface. The main unit of work in DTS is a package, which serves as a container for various components.

There are four types of components in a DTS package:

• Connections: Represent data sources or targets and are implemented using OLE DB providers. SQL Server DTS provides built-in connections for various data sources such as SQL Server, Access, Excel, Visual FoxPro, text and HTML files, as well as third-party databases like Oracle, Paradox, and dBase.
• Tasks: Represent individual actions such as data transition or transformation. Tasks can also provide status notifications.
• Precedence constraints: Determine the conditions necessary for a task to execute, creating additional logic in the package workflow.
• Global variables: Values or sets of values that can be shared among components of the same package.

There are several ways to create DTS packages. The simplest one is to use the available Wizards, such as the DTS Export Wizard and DTS Import Wizard. These wizards guide you through a sequence of steps to create a new package. However, this method has limited configuration options.

The recommended tool for creating complex packages is the DTS Designer. It provides a balance between ease of use and a wide range of configuration options. You can launch the DTS Designer by selecting the New Package option from the context-sensitive menu of the Data Transformation Services node in the SQL Enterprise Manager.

Once you have created a package, it is a good practice to save it before executing it. This allows you to reconfigure or rerun the package later and also enables you to schedule it for execution. Packages can be stored in different locations, each with its own benefits and drawbacks.

The main options for storing packages are:

• Sysdtspackages table: This option corresponds to the Local Packages node under the Data Transformation Services folder in the SQL Enterprise Manager. It supports versioning, allowing you to access and edit previous versions of a package. You can also assign user and owner passwords for executing and editing the package.
• Microsoft Repository: This option corresponds to the Meta Data Services Packages node under the Data Transformation Services folder in the SQL Enterprise Manager. It allows you to keep track of package history, including version numbers and lineage information. Lineage provides the means for tracking changes that occur during package execution, which is useful for auditing purposes.
• Structured storage files: These files, with the extension .DTS, are ideal for copying packages between SQL servers. You can store one or more packages within a .DTS file and use traditional file transfer methods to deliver it to a target SQL server. These packages can be secured with passwords and can be executed directly using the DTSRUN.EXE command line utility.
• Visual Basic module files: These files, with the extension .BAS, are intended for Visual Basic developers who want to use programming methods of the DTS object model to modify existing packages. These packages are not intended to be re-imported into DTS Designer or Repository.

Once you have created and saved a package, you can execute it interactively or schedule it as a job. Interactive execution can be triggered from the DTS Designer window or from the Command Prompt using the DTSRUN utility. You can also use the xp_cmdshell extended stored procedure or the DTSRUNUI utility to execute a package.

When scheduling packages, it is recommended to enable logging, which simplifies troubleshooting in case of a job failure. Logging provides status, progress, and error messages for each step of the package execution. Logging can be recorded into a text file or MSDB database tables.

It is important to consider the security context in which a package is executed. During interactive execution, a package operates using the same account as the currently logged-on user. Scheduled packages, on the other hand, always operate as the SQL Server Agent service account.

In conclusion, Data Transformation Services (DTS) in SQL Server provide a powerful and flexible way to transfer and transform data between diverse data sources. By understanding the concepts and components of DTS, you can efficiently manage data integration tasks and improve the overall performance of your SQL Server environment.

Stay tuned for our next article, where we will explore the DTS wizards in more detail.