What is a DTS package?

A DTS package is a set of related objects (connections, tasks, and workflows) that can be used to access, transform and manipulate data from a wide range of sources including text files and relational databases. DTS was introduced with the release of SQL Server 7.0 and was carried across to SQL Server 2000 because of its immense popularity.

To create DTS packages, we use the DTS designer (which is accessible through enterprise manager). We will talk more about the DTS designer in a minute, but for now all we need to know is that there are two types of objects that it can create: connections and tasks.

A connection object represents a connection to a data store, such as SQL Server 2000, an Oracle database, or even a text file. They are used to give tasks access to the data they need to transform or manipulate. A task object allows us to work with data accessed through connection objects. Tasks allow us to copy, query, or manipulate data and are generally responsible for providing any sort of activity in a DTS package.

The DTS designer allows us to do more than just access and manipulate data, however. It includes a set of tasks that allow us to transfer files using FTP, send messages to an MSMQ queue, or even create our own ActiveX scripting tasks using VBScript, JScript or PerlScript. By combining all of the connections and tasks that are made available to us through the DTS designer, we can create some fairly complex data-related packages.

Let's take a look at the DTS designer now. Load Enterprise Manager (Start -> Programs -> Microsoft SQL Server -> Enterprise Manager) and expand both the Microsoft SQL Servers and SQL Server Group nodes in the left column. Next, expand your database node as well as that databases Data Transformation Services node. Enterprise Manager should look something like this:

Right click on the Local Packages leaf and choose the New Package option. This will load the DTS package designer. Notice the two sets of objects down the left side of the toolbar? These are the connection and task objects that we will use to create our sample DTS package. The huge area of white space on the right is where the objects that we create as part of our package will go. Click on the icon for any connection or task object to add it to our currently un-saved DTS project. Notice how the object appears in the white space on the right, like this:

Take a look at the options in the menu bar across the top of the DTS designer window. The package menu allows us to save our packages and modify their properties amongst other things. The connection and task menus display exactly the same icons as in the left pane, but they also include descriptions.

The last menu is the workflow menu. The workflow menu contains three options: On Completion, On Success, and On Failure. Each object in our DTS package can respond to these events (which represent the completion of a task, the successful completion of a task, and the unsuccessful completion of a task respectively), and we can optionally use relationships to link our objects to tasks that should be executed when SQL Server fires these events.

Before we move onto creating our sample DTS package, take a look at some of the more important connections and tasks that are available to us through the DTS designer:

Connections:

•   Microsoft OLEDB Provider for SQL Server: Allows us to define a connection to any OLEDB compatible data source such as SQL Server 2000, Access, Paradox, dBase or MySQL.
•   Microsoft Access: Allows us to define a connection to a Microsoft Access database.
•   Microsoft Excel: Allows us to define a connection to a Microsoft Excel database.
•   Text File: Allows us to define a connection (either source/destination) to an ASCII text file. Useful when using comma-separated valued flat-file tables.
•   Microsoft Data Link: Allows us to define a connection to a data source via a UDL (Universal Data Link) file.

•   ActiveX Script Task: Allows us to use an ActiveX scripting language to interact with other tasks, instantiate COM objects, etc.
•   Transform Data Task: Allows us to define source-and-destination relationships between connections.
•   Execute SQL Task: Allows us to build a query against the data source of a connection object. We can then manipulate the results of this query as a recordset through an ActiveX scripting task object.
•   File Transfer Protocol Task: Allows us to setup a real-time FTP connection to a remote host and work with the files on that host.
•   Copy SQL Server Objects: Allows us to copy data from one source to another.

	1 2 3 4 5 6