Using Data Transformation Services - Part 1 - Import Export Wizard
(Page 2 of 5 )
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 settings\computer user\
my documents\fromaccess.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.
Next: The DTS Designer >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 37 
