Using DTS: Import From/Export To a Non-Microsoft Database

In this tutorial all the steps will be shown for importing from or exporting to the Sql Anywhere database, and since the steps are essentially the same only   few steps will be shown for importing into SQL Server. The reader must review the previous tutorials to get a better handle.

The following two pictures show screen shots of the databases with tables showing in their respective administrative user interfaces, the Enterprise Manager for MS SQL 2000 server and the Sybase Central for the SQL Anywhere server (version 9.02). The publishers table from MS SQL Server will be moved to the ASAdemo.db on the SQL Anywhere Server, and the department table from the SQL Anywhere will be imported into the pubs database in MS SQL Server.

Initial preparation before calling DTS

Before you move tables from one system to the other, you need to make some initial preparations to the system so that the operation completes without a hitch.

The MS SQL Server must be started since we are operating with its database. Also for visualizing and verifying transfer, the Enterprise manager should be running, although it can be started when needed.

SQL Anywhere server must be started, and Sybase Central, which is the counterpart of the Enterprise Manager, must be started and connected. The SQL Anywhere server can be started from Start ->All Programs ->Sql 9 Anywhere ->Adapter Server Anywhere ->Network Server Sample. For details about this please review my previous article. You should see the following screen when the server starts.

Sybase Central can be launched by following the trail, Start -> ALL Programs-> Sql 9 Anywhere ->Sybase central. When it is launched it is not connected to the server as shown here.

You can connect to the server started in the previous step by using the drop down in the file menu, File -->Connect. When you click on connect, you will face the following window which requires authentication. For username enter dba and for password enter sql and click OK.

Clicking on the Database tab, the next screen will be shown. If you click on Find..., the dropdown should show the asademo9 server, assuming you have started the database server. The database is identified by a file name in the C: directory, and therefore you may browse to that file. In the present example the complete path to the directory is C:\Program Files/Sybase/SQL Anywhere 9/asademo.db.  Enter the same information in the dropdown marked Start Line: Please refer to the previous tutorial for other details.

Create a File DSN for SQL Anywhere

In this section we shall make a file datasource for the SQL Anywhere database that can be used for moving the tables in and out using DTS.

The Start -> All Programs->Control Panel->Administrative Tools->Data Sources (ODBC) trail will open up the ODBC Data Source Administrator window (this is in windows XP Professional). Clicking the File Data Source tab will open up the window of file data sources. If you can identify a window which you had configured earlier, you may be able to pick up that data source. For creating a new datasource as in this tutorial, click on the Add... button. This will open up the next window shown. The Adaptive Server Anywhere 9.0 driver is first in the list. We will use this driver that came with the SQL Anywhere 9.0 download. Click Next.

In the Create  New Datasource Window type a name for your database; here it is called SqlAny. Click on Next> which will bring you to the summary of actions you have taken so far. This  successfully  creates a File Data Source in the location, C:\Program Files\Common Files\ODBC\Data Sources\SqlAny.dsn.

Clicking on the button Finish will bring up the next screen, where you enter the same credentials you used earlier.

Click on the ODBC tab. You will see the following screen, where you will not be configuring anything. 

Click on the Database tab. You will enter essentially the same information you entered for Sybase Central earlier in this tutorial.

You may click on the Browse...button which opens up the following screen. This is the directory where the asademo.db database was installed initially.

If you highlight asademo.db and click Open, you fill up the Database file: text box. Copy and paste the same to the Start Line: text box. Accept other defaults.  Do not worry about the other two tabs for the present task. Click OK to screen one above. You come back to the ODBC data Source Administrator's screen, but you will see the dsn you created in the listing as shown by the highlighted item. This completes your task to create a file DSN for the SQL Anywhere database.

Export a table from MS SQL to SQL Anywhere

We have seen in an earlier tutorial how to invoke the Import/Export wizard. You could start this wizard from the shortcut on Start ->All Programs->MS SQL Server-> Import Export Data. This will actually start the C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtwiz.exe program. It could be started from the command line as well. This starts an instance of the Import/Export wizard as shown in the following image:

Click Next to pick the source of data. Since we will be exporting from the local SQL Server, we choose the pubs database on the local server as shown in the next screen shot.

Click Next> to open the next screen, where we pick a destination. We choose from the drop-down the Sybase Adaptive Server anywhere Provider 9.0. We may need to connect to the SQL Anywhere server (assuming it is still running). Click on the Properties...button.

This opens up the next screen where you enter the credentials one more time. The only information needed is as shown in this screen. You may test to verify that you succeeded in the connection.

Close the message box by clicking OK and then click Next>. This will get you to the following screen  showing that you are ready to move the table from MS SQL to SQL Anywhere.

When you click Next> to the above screen you will open the next screen where you pick the table to move. We will be moving the [pubs].[dbo].[publishers]  table to the SQL Anywhere.

Place a check mark against this. If you want you may see the data by hitting the Preview button. Now click Next>. You will see the following screen.

This is essentially as the screen succinctly puts it, the Save, Schedule, and replicate package screen. We will discuss elsewhere some of these options. For now, place a check mark against the Save DTS Package option at the default SQL Server and click Next>. This brings up the screen where you need to save the package with a name. Here it is saved to Export ToSqlAny.

Click Next> to open the next screen. This completes the wizard's main tasks in exporting the table.

Click finish. This starts the export as seen in the next screen. The publishers table can now be found in the Sybase's asademo.db database.

Now we open up the Sybase Central Administrative interface and refresh all objects. We see the publishers table in the asademo.db database showing that the process was successful.

Import a table from SQL Anywhere to MS SQL

In this case only a few screen shots will be shown. The steps are exactly the same except that you will be importing the department table from asademo.db to MS SQL Server's pubs database. Just before the package is run, it is saved as ImportFromSqlAny in the SQL Server. The table imported is shown here. A table [pubs].[dbo].[department] is created and the data is copied into it as shown here.

This next picture shows the pubs database in Enterprise Manager displaying the department as one of the tables in the pubs database.

The above table shows that the import was successful.

In the example shown the import/export worked without any problem. Comparing the table design, the following observations were made looking at table design and data from these operations.

While exporting:
Char and varchar goes over to varchar.
Size of char and varchar go over correctly.
Allow Nulls go over correctly.
Column Default does not go over.
Primary Key information will not go over.
Data went over without any truncation.

While Importing:
Char gets into varchar.
Integer of unspecified size gets into int with length=4.
Primary key information will not come over.
Allow nulls come over correctly.
Data came over without any truncation.
 

The tutorial provides a step-by-step procedure for moving tables from/to a MS SQL server from an SQL Anywhere server. The example shows the successful transfer of tables, however, this may not always be the case  due to restrictions imposed by either side as to data type, size, and so forth. Although connections were made starting from the ODBC data Source Administrator, they can also be set through the SQL wizards by choosing the appropriate options. The ODBC driver bundled with the SQL anywhere appears to be adequate. However, one could also use the MS OLEDB for ODBC driver.