Skip to content

TechRepublic

  • Top Products
  • AI
  • Developer
  • Payroll
  • Security
  • Project Management
  • Accounting
  • CRM
  • Academy
Resources
  • TechRepublic Premium
  • TechRepublic Academy
  • Newsletters
  • Resource Library
  • Forums
  • Sponsored
Go Premium
Popular Topics
  • Top Products
  • AI
  • Developer
  • Payroll
  • Security
  • Project Management
  • Accounting
  • CRM
  • Academy
  • Project Management
  • Innovation
  • Cheat Sheets
  • Big Data
  • Tech Jobs
View All Topics
Go Premium
Data Management

Customize data transfers with DTS and SQL Server

By Bill Detwiler September 13, 2006, 4:24 AM PDT Bill Detwiler on Twitter billdetwiler

Image
1
of 8

r00220020711wrr01_01.gif
r00220020711wrr01_01.gif
Customize data transfers with DTS and SQL Server

DTS Designer

DTS Designer

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.

In this screenshot gallery, Stephen Warren shows you how to customize data transfers with SQL Server and DTS.

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

Click each image for a full size version.

Customize data transfers with DTS and SQL Server

Task options

Task options

There are 17 task options to choose from.

Customize data transfers with DTS and SQL Server

Create a new package

Create a new package

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.

Customize data transfers with DTS and SQL Server

Connection Properties

Connection Properties

Next, choose Microsoft Excel 97-2000 and specify the Excel document.

Customize data transfers with DTS and SQL Server

Select the connections

Select the connections

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.

Customize data transfers with DTS and SQL Server

Build the query

Build the query

The DTS Designer will present the Transform Data Task Properties dialog box, where you can build your query.

Customize data transfers with DTS and SQL Server

Data Transformation Services

Data Transformation Services

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, and right-click and execute the package.

Customize data transfers with DTS and SQL Server

Adding e-mail notification

Adding e-mail notification

You can take this example one step further by adding a Send Mail task so that every time you run the package, it sends the YTD spreadsheet to the appropriate managers.

  • Data Management
  • Account Information

    Share with Your Friends

    Customize data transfers with DTS and SQL Server

    Your email has been sent

Share: Customize data transfers with DTS and SQL Server
Image of Bill Detwiler
By Bill Detwiler
Bill Detwiler is the Editor for Technical Content and Ecosystem at Celonis. He is the former Editor in Chief of TechRepublic and previous host of TechRepublic's Dynamic Developer podcast and Cracking Open, CNET and TechRepublic's popular online show.
  • Account Information

    Contact Bill Detwiler

    Your message has been sent

  • |
  • See all of Bill's content

Daily Tech Insider

If you can only read one tech story a day, this is it.

TechRepublic TechRepublic
  • TechRepublic on Facebook
  • TechRepublic on X
  • TechRepublic on LinkedIn
  • TechRepublic on YouTube
  • TechRepublic on Pinterest
  • TechRepublic RSS
Services
  • About Us
  • Newsletters
  • RSS Feeds
  • Site Map
  • Site Help & Feedback
  • FAQ
  • Advertise
  • Do Not Sell My Information
  • Careers
Explore
  • Downloads
  • TechRepublic Forums
  • Meet the Team
  • TechRepublic Academy
  • TechRepublic Premium
  • Resource Library
  • Photos
  • Videos
  • Editorial Policy
  • Legal Terms
  • Privacy Policy
© 2025 TechnologyAdvice. All rights reserved.
CLOSE

Create a TechRepublic Account

Get the web's best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let's start with the basics.

Already registered? Sign In
Use Facebook
Use Linkedin

* - indicates required fields

CLOSE

Sign in to TechRepublic

Not a member? Create an account
Use Facebook
Use Linkedin

Lost your password? Request a new password

CLOSE

Reset Password

Please enter your email adress. You will receive an email message with instructions on how to reset your password.

Check your email for a password reset link. If you didn't receive an email don't forgot to check your spam folder, otherwise contact support.

Back to login
1 Finish Profile
2 Newsletter Preferences
CLOSE

Welcome. Tell us a little bit about you.

This will help us provide you with customized content.

No thanks, continue without
1 Finish Profile
2 Newsletter Preferences
CLOSE

Want to receive more TechRepublic news?

Newsletter Name
Subscribe
Daily Tech Insider
Daily Tech Insider AU
TechRepublic UK
TechRepublic News and Special Offers
TechRepublic News and Special Offers International
Executive Briefing
Innovation Insider
Project Management Insider
Microsoft Weekly
Cloud Insider
Data Insider
Developer Insider
TechRepublic Premium
Apple Weekly
Cybersecurity Insider
Google Weekly
Toggle All
No thanks, continue without

You're All Set

Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.

Back to Home Page
×