Migrating Tables and Data

Using Data Transformation Services (DTS) allows you to import and export data between multiple heterogeneous sources that use an OLE DB–based architecture such as Microsoft Excel spreadsheets and flat text files, and to transfer databases and database objects (for example, indexes and stored procedures) between multiple computers running Microsoft SQL Server version 7.0. You can also use DTS to transform data so it can be used more easily to build data warehouses and data marts from an online transaction processing (OLTP) system.

The DTS Wizard allows you to interactively create DTS packages that use OLE DB and ODBC to import, export, validate, and transform heterogeneous data. The wizards also enable you to copy schema and data between relational databases.

Use the DTS Wizard to transfer your Sybase data into Microsoft SQL Server in a few steps.

1. Launch Enterprise Manager, click the Tools menu, and choose Data Transformation Services, Import into SQL.
2. In the Choose a Data Source dialog box, choose Sybase System 11 as the Source. Choose the DSN that corresponds to your Sybase data source.
3. In the Choose a Destination dialog box, choose Microsoft SQL Server 7.0 OLEDB Provider, select the database server, and then choose the required authentication mode for the selected database server.
4. In the Specify Table Copy or Query dialog box, choose Copy tables.

5. In the Select Source Tables dialog box, choose Select All. Click the gray box found within the Transform column of the Select Source Tables dialog box to change column names, data types, nullability, size, precision, and even write code to make unique transformations to your data before importing data into Microsoft SQL Server.

6. Run the data migration package immediately, or run it at a later time. The DTS Wizard shows you the progress and status of the data migration, step by step.