Using DTS: Import From/Export To a Non-Microsoft Database - Import a table from SQL Anywhere to MS SQL

(Page 5 of 5 )

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.

Comparison Notes

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.
 

Summary

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.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

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

· Come read the article and comment! The method is quite generic. You can...    · Hi, You say that DTS can be used independantly of MS SQL Server but how would you...    · Hi skippy:Try this free download of MSDE [Download Microsoft SQL Server 2000...    · How do we cary over the primary keys when porting data from sybase to sql...    · I am just returning from a vacation, and I will try to show it through a demo...   >>> Post your comment now!