Using Data Transformation Services – Part 1

Data Transformation Services (DTS) enable transfer of data and objects between different SQL 2000 Server systems. This article includes an overview of the DTS package, as well as step-by-step instructions for transferring a table from the SQL Server to an MS Access file.

SQL-DMF: SQL Distributed Management Framework is a set of utilities that are not part of the database engine (like Query Analyzer and Enterprise Manager). There are three application programming interfaces, or utilities that allow us to access all the objects in the SQL Server. They are:

  • SQL Namespace – SQL-NS
  • SQL Distributed Management Objects – SQL-DMO
  • SQL Data Transformation Services – SQL-DTS

DTS, which stands for Data Transformation Services, enable transfer of data and objects between different SQL 2000 Server systems. DTS was originally created to transfer data from OLTP data into OLAP data, which included the various steps of extracting the data, cleansing and transforming to remove inconsistencies, and then loading the data (popularly known as ETL). DTS can validate and summarize data while transferring — making it extremely powerful.

DTS is available to all SQL Server editions from Enterprise to MSDE. DTS uses OLEDB, so it can be used for transferring data from one OLEDB to another OLEDB and therefore is not specific to SQL Server. It could be used to transfer data between any OLEDB supported databases, for example between Oracle to MS Access without an SQL 2000 middle man. Since Microsoft OLEDB for ODBC can connect to ODBC sources, the data transfer functionality is extended to ODBC sources, and DTS assumes an even more powerful role.

The DTS Data Pump is at the heart of the transfer process connecting from source to sink. While data is being transferred, it may also be transformed by using the ActiveX scripting languages like jscript and vbscript. This special feature operates on rows of data where column contents from the source side can be made to match up with what is required by the destination, or even more demanding complex functions, as well as validated retrieval from external sources to local source can be accomplished.

Contents of this tutorial

There are two tools and an API available in SQL 2000 Server to interactively create DTS Packages. These are:

  • Import Export Wizard
  • DTS Designer
  • DTS API

The first part of this tutorial shows a step-by-step approach for transferring a table from the SQL Server to an MS Access file. In the second part, an overview of the DTS package is presented. This is followed by the presentation of a concrete step-by-step example of copying a database from an SQL Entrprise Server to an MSDE server in the same machine. A future tutorial will consider the DTS API in considerable detail. While the Import Export Wizard is quite powerful, using the DTS Editor will provide a lot more power and flexibility to fine tune the data transfer.

{mospagebreak title=Import Export Wizard}

If the SQL 2000 Server node is expanded in the SQL 2000 Server Enterprise Manager, the DTS is one of the main nodes, as shown in this picture:

We will consider exporting a table from the “pubs” database to an MS Access database called From Access in the file system of the machine, which also hosts the SQL 2000 Server.

Click the pop-up menu item Export Data … which brings up the Import Export Wizard as shown in the next picture. This is a completely Wizard supervised transfer of data.

Clicking Next brings up the following window, where you need to supply information about where the data is coming from (Source). In this case, it’s an export of data, and therefore the data is coming from the Local SQL server. You also need to supply authentication information, which in this case is “Windows Integrated Authentication.” This example considers transferring a single table from the pubs database, and therefore the database that should be chosen is pubs. These choices are shown in the following window.

If the data has changed since the beginning of the transfer process, the database can be refreshed. Clicking Next will take you to the next window, where you need to enter information for the destination database. The configurable items are Microsoft Access (the Jet Provider), the file name of the MS Access database (a file by name should exist; create an empty Access file with the chosen name, in this case FromAccess in default directory), and authentication information (in the case of database created by the administrator, this could be blank) as shown in the the next picture:

Clicking on Next in this window takes you to the next window, where the possibility of copying table|tables|view|Views or results of a query can be chosen. Since a single table will be exported to MS Access, the default radio button choice is appropriate.

Clicking on Next takes you to the window where you can choose what you want to transfer, as shown. In this example, the employee table will be transferred, so a check mark is placed as shown. Clicking on preview shows the data from the chosen object, and clicking on the under Transform will bring up the Column Mappings and Transformations. The mappings and transformations will be discussed in a future tutorial. For the present, the defaults are accepted.

Clicking on Next will bring up a window where you need to say when? you want to run (execute) this package, or if you are saving, where? you want to save the DTS Package. You may also run and save at the same time. If you choose to save, it can be saved in different places in different forms as shown. In the present case, the package will be saved to the SQL Server and also will be executed. You may also schedule when to run the package by clicking and configuring the ensuing wizard dialog.

Clicking Next takes you to this window, where you provide a name and a description to the package as shown here.

Clicking Next completes the configuring of the DTS. This screen also shows the summary of all the interactive actions taken so far:

Source: Microsoft SQL Server
Using Microsoft OLE DB Provider for SQL Server
Location: (local)
Database: pubs

Destination: Microsoft Access
Using Microsoft Jet 4.0 OLE DB Provider
Location: c:documents and settingscomputer user
                      my documentsfromaccess.mdb

Tables
[pubs].[dbo].[employee] -> employee

Save package in SQL Server as: SqlToAccess in (local)

When you click Finish, the window closes to open the progress window, where you can monitor the progress of the transfer. If some information is not transferred you may get messages as to which of the steps failed in the transfer. Since you also want the package saved, you get a step for this process as well.

You may verify that the FromAccess database now contains the employee table from the pubs database as shown here.

Since a package was also saved, the package can be viewed in the place where it was saved, namely, the DTS in the SQL Server as shown here. In the title bar you can see the package name we gave. The two connections are the source and destination, and the connecting arrows are the tasks. These will be discussed in more detail later.

{mospagebreak title=The DTS Designer}

The DTS Package is a basic unit of DTS. The DTS package contains all the information (source, destination, tasks, transformations, and workflows) about the transformation, whether it is an import or an export. The DTS editor in SQL 2000 Server can create and execute packages.

In order to get an understanding of the DTS Designer tool, open the SQL 2000 Server Enterprise Manager. Expand the Data Transformation Services folder to reveal the different child nodes. You will be seeing the Local Packages, the Meta Data Services Packages, and Meta Data. We will come back to this later. Now right click on the Data Transformation Services folder to access the DTS Package editor by choosing to create a New Package as shown in the next picture. It opens up the window where packages can be created. The left-hand panel has the tool box for Connections and Tasks, while the right-hand panel is empty when opened to create a new package. This is an area where the package is designed.

A package is an entity which has all the transfer specifications of the DTS, as discussed earlier. It is the basic container of all the information. It has the following components necessary for fulfilling its task: the Connections, the Tasks, the Transformation and the WorkFlow as shown by the menu items in the DTS editor, as seen in the next picture. It is also possible to have multiple editors open (two are shown).

The Connection component consists of all the different possible connections that are supported by the SQL 2000 Server. Each of the connections can be readily identified by placing your mouse on the object and reading the pop-up text as shown. These can also be accessed by clicking the menu item connection when the drop down box reveals the same information. The various connections possible for an out of the box installation of SQL 2000 Server are shown in the next picture including both Microsoft proprietary as well as others:
 

The Task component of DTS is rich and can handle a number of tasks(some 19 of them), and these are seen by clicking the Task menu item in the DTS Package editor GUI. These include tasks for querying, copying database objects, transferring objects, transferring logins and a host of other tasks. The DTS tasks, like the connections, can be dragged from the left pane and dropped into the design pane to its right. In addition to these, it is possible to register and unregister a custom task by providing the dll.

Microsoft OLEDB
  OLE connection to an SQL Server Provider for an SQL Server Datasource
Microsoft Access   OLE provider for JET while connecting to an Access Database
Microsoft Excel 97-2000   Connecting to an Excel Spreadsheet(xls)
dBase5   Defines connection to an dBase 5 database
HTML (Source)   Defines connection to an HTML page
Paradox5.x   Defines connection to Paradox 5 database
TextFile (Source)   Defines connection in order to read text file
Text File (Destination)   Defines connection in order to write to a text file
Microsoft ODBC for Oracle   Defines connection to an Oracle Database using the ODBC driver
for Oracle
Microsoft Data Link   Defines connection to an existing UDL (Universal Data Link) file
Other Connection   Define connection using Microsoft OLEDB provider for ODBC

The DTS transformations component takes care of transformations involved in DTS. String, Date Time String transformations, ActiveX Script transformation and file I/O transformations are all handled by this component. These built in column transformations also include copy column transformation.

The DTS workflow component can be accessed by the menu item as shown. The On Completion, On Success, and On Failure workflow items orchestrate the workflow based on the outcome of the result of the current step in the process. For example, On Success dictates that the next step defined in the work flow can only be executed on the successful outcome of the current step, and so on.

What is accomplished by the package

Let us consider the simple exercise of copying a database from one SQL 2000 server to another. Specifically, two SQL 2000 servers are considered, one of which [XPHTEK] is a SQL 2000 Server (Developer build) and the other [XPHTEK/Test] is a MSDE 2000 (Desktop Engine on Windows NT 5.1). Both servers are installed on the same machine. There is a database [TestWiz] on the XPHTEK which will be copied to the [XPHTEK/Test] server. Hence a very simple, single object package will be created on the XPHTEK.

{mospagebreak title=Package creation details}

Since a database object is being copied, the Copy SQL Server objects task is the appropriate task to be completed. Drag the Copy SQL Server Objects task from the left panel to the right. This opens up the wizard with three tabs: Source, Destination and Copy. The Source tab requires a description of the task, the server on which the source data is stored, and its authentication information. When all this information is keyed in, the window appears as shown in this picture:

When you click on OK to this window, you will be asked to provide information for the destination Server, where this copied data is to be stored. If a named object already exists, you can access it from the dropdown menu after specifying the database (in this example XPHTEK/Test), or choosing to create a new database.

Since the new database requires a name, you need to create a name. Since the database needs the creation of the data and log files, the needed file sizes can be configured. If the defaults are accepted, as in this case, each of them will be 2MB. Clicking on the Help button will open the context sensitive help item of creating a database from the SQL Server book on line. The name of the database chosen is [CopiedTest]. When you click on OK, the DTS has all the information (source and destination) for creating the database.

In the next tab, Copy, a number of parameters are configurable. Copying tables, data, stored procedures, referential integrity constraints, indexes, and so forth will be carried out. If Data already exists (in this example, new data is created and therefore is not relevant), it can copy over, or append. If you choose not to copy all objects, by deselecting the checkbox you will be able to select objects that you need to copy (it is assumed that all objects are copied in this simple example) and if you choose not to use default options, by unchecking this, you may be able to specify the options you want. The chosen configurations for this example are shown in the next picture. The creating database script will be stored in a file directory, the default being, “C:Program FilesMicrosoft SQL Server80Tools”

When you click on OK in the final tabbed window, the package creation will be completed. You will be able to see the package, cpTestWiz_to_XPHTEK/Test, in what was an empty design panel to start with, as shown:

Now this package can be saved and given a name [Jan2005_1] as shown here, by bringing up this window and clicking the menu item package, and using the SaveAs option.

Now the newly created package appears in the list of Local packages as shown in the next picture:

{mospagebreak title=Executing the package/step}

The package created can be executed by right clicking the package icon in the DTS Editor and choosing ExecuteStep, by choosing to execute from the menu item Package -> Execute, or by choosing Local Packages-> Jan2005_1 package in the file list and right clicking it to choose Execute Package. After taking the later two of these actions, the following screen comes up, executing the package immediately and, when finished, the next screen shows up, informing that the package was successfully executed. Execute step does not show the process being executed; it shows only the successful outcome window:

Here are the pictures of the explorer view of XPHTEK/Test before and after copying the database:

before copying

aftr copying the database

Summary

In this tutorial on Data Transformation Services, two of the important tools of the SQL 2000 Server were discussed, the Import Export Wizard and the DTS Editor. A package creation using the Import Export wizard was described. Executing the package immediately initiates and completes the export process as well as creating a package for future reuse.

The concrete step-by-step process of copying a database from one SQL server to another SQL server edition using the DTS editor was also described. The tutorials to follow will discuss the DTS API, the slightly more involved data transfer from a non-Microsoft product to a non-SQL server based data source, and so forth. DTS is a very powerful tool that makes data transfer a less arcane, more productive process. The DTS designer is an extremely easy to use interface, but at times some of the information stored in the wizard’s form disappears (replaced by defaults) after one execution step if not saved immediately. This may be just a local malady, but forewarned is forearmed.

3 thoughts on “Using Data Transformation Services – Part 1

  1. Great article!
    One wonders if there is any way to transform data from a SQL Server database file into MS Access from the MS Access side without having SQL Server?

  2. The given topic above is really so helpful for me in working the creation of DTS packages. If any more related information please provide it we all can use it.

[gp-comments width="770" linklove="off" ]