Importing/Exporting Data  «Prev  Next»

Lesson 7DTS Package Designer
Objective Use DTS Designer to create a package.

DTS Package Designer in SQL Server

Microsoft provides a graphical tool for creating and executing packages called DTS Package Designer. This tool allows you to create and execute local packages or packages residing in a central repository.

While there are multiple ways to start DTS, the simplest way is to follow these steps. The image below explains this topic in greater detail.
Starting DTS
Starting DTS

  1. Bring up SQL-EM
  2. Select the server group
  3. Select the server
  4. Select Data Transformation Services
  5. Select either local or repository package
  6. Right-click to bring up the New Package menu
After you select New Package, the DTS Package Designer will start up. There are three main areas of the DTS Package designer:
  1. The task bar
  2. The data objects
  3. The design areas

The MouseOver below illustrates these areas.
The WorkFlow menu allows you to define a transformation from one or more data sources to a destination
  1. The WorkFlow menu allows you to define a transformation from one or more data sources to a destination. You can also specify what to do on completion of a task for failed tasks, completed tasks, and successful tasks.
  2. The WorkFlow menu allows you to define a transformation from one or more data sources to a destination. You can also specify what to do on completion of a task for failed tasks, completed tasks, and successful tasks.
  3. The Design sheet will graphically show you the package you are working on.
  4. The Design sheet will graphically show you the package you are working on.
  5. The Design sheet will graphically show you the package you are working on.
  6. The Tasktool bar lists the standard tasks that can be performed.
  7. The Datatool bar lists the common data types.
The WorkFlow menu allows you to define a transformation from one or more data sources to a destination. You can also specify what to do on completion of a task for failed tasks, completed tasks, and successful tasks.

dts designer
There are standard tasks, data objects, and workflow items for use with the DTS Package Designer. While all tasks and all data objects require similar information, each has its own distinct use and will prompt you for specific information needed for the task or to access the data.

Tasks and data objects

The following MouseOver illustrates the different standard tasks and standard data objects used with the DTS Package Designer.

The Active Script Task allows you to define a Visual Basic ActiveX script or Java ActiveX script to be executed
  1. The Active Script Task allows you to define a Visual Basic ActiveX script or Java ActiveX script to be executed.
  2. The Execute Process Task allows you to specify a process to run, the processes parameters, and the processes successful return code.
  3. The Execute SQL Task allows you to specify any SQL to execute. You can also call the Query Designer from here to graphically design a query.
  4. The Data Driven Query Tasks allows you to transfer data from one data source to another, either moving data from a table or moving data based upon a query.
  5. The Transfer SQL Server Objects Task allows you to transfer all objects from one SQL Server 7.0 database to another SQL Server 7.0 database.
  6. The Send Mail Task allows you to send email as part of a package.
  7. The Bulk Insert Task will perform a bulk insert of a text file into SQL Server 7.0 or greater.
  8. The Microsoft OLE DB Provider for SQL Server allows you to specify an OLE DB connection to SQL Server.
  9. The Microsoft Access data object allows you to specify a connection to a MS Access database.
  10. The MS Excel 8.0 data object allows you to specify a connection to a MS Excel file.
  11. The dBase 5 data object allows you to specify a connection to a dBase 5 database.
  12. Paradox 5.x data object allows you to specify a connection to a Paradox 5.x database.
  13. Text File (Source) object allows you to specify a read connection to a text file.
  14. Text File (Destination) object allows you to specify a write connection to a text file.
  15. Microsoft ODBC Driver for Oracle object allows you to specify a connection to an Oracle database.
  16. Microsoft Data Link data object allows you to specify a connection to OLE DB resource.
  17. The Other Connection data object allows you to specify a connection to an ODBC data source.

DTS Designer Tasks

Save a package

After you have created a package, you will normally want to save it. The DTS Package Designer allows you to save a package to the local server, a central repository, or a file. To save a package, select the Package menu, then select Save.

Saving a package
Saving a package
In the next lesson, you will learn how to schedule and run a package.

DTS Package Designer Buttons

Click the link below to view information regarding the functions of DTS Package Designer.
DTS Package Designer Buttons