Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 27, 2006

SQL Server DTS Designer

By Steven Warren

Prior to SQL Server 2000, the only way to back up or export databases from SQL Server to other databases was to use the bulk copy program. We all spent hours configuring scripts to do miraculous things using this cumbersome method. Then, when we finally mastered the art, SQL 7.0 was released with a GUI to accomplish automation that was both intuitive and simple to use. Microsoft called this interface Data Transformation Services (DTS).

DTS is a set of related tools that enable you to manipulate or transform data from various sources. To create a DTS package, you can use the DTS wizards to move data. You can also use the more advanced DTS Designer, which lets you create a multitude of packages and workflows. You can connect to a data store, create tasks that allow you to FTP data, and create your own tasks using a scripting language. Adding DTS to your repertoire will bring a whole new dimension to your skill set.

Creating a package

To access the DTS Designer, open Enterprise Manager from the Start Menu, right-click on Data Transformation Services, and choose New Package. Figure A shows the New Package window.

There are 11 connection options (Figure B) and 17 task options (Figure C) for you to choose from.

Let's create a package that will do the following:

  • Query our sample pubs database for a list of authors and their year-to-date sales
  • Save the results to an Excel spreadsheet

First, open the DTS Designer and add the Microsoft OLE DB Provider for SQL Server connection. Specify the pubs database and the authentication method, as shown in Figure D.

Figure D.

Next, choose Microsoft Excel 97-2000 and specify the Excel document, as shown in Figure E.

Figure E.

Once you have created your source and destination, hold down the [Ctrl] key and select both the OLE DB and the Excel connections. Then, choose the Transform Data Task, as shown in Figure F. The DTS Designer will present the Transform Data Task Properties dialog box (Figure G), where you can build your query.

Figure F.

Figure G.

You can choose the Destination tab to view your table and the Transformations tab to define the transformation between your source database and your destination database. When you are finished, click OK and save your package by clicking the Save button on the toolbar. Enter the name and location of the package and click OK.

Now you are ready to view your packages and test them. To do this, open Enterprise Manager and expand Data Transformation Services. Choose Local Packages, as shown in Figure H, and right-click and execute the package.

Figure H.

Figure I.


DTS offers an abundance of options that will help you meet the needs of your company. The example we worked through here was pretty basic, but the DTS Designer enables you to perform far more sophisticated data manipulation. For instance, you can transfer logins from one database to another, copy tables to FTP sites, and even script your own tasks. The more you experiment with DTS, the better you will become at manipulating data. If you have never used DTS, this article will help guide you as you become familiar with the various connections and objects in DTS Designer.

» See All Articles by Columnist Steven S. Warren

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM