Using Data Transformation Services - Part 1 - The DTS Designer
(Page 3 of 5 )
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.
Next: Package creation details >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 37 
