How to use data transformation services (DTS) to export data from a Microsoft Access database to an SQL Server database

DTS provides the functionality to import and export data between SQL Server and any OLE DB or ODBC data source, including Microsoft Access. SQL Server includes DTS and the DTS Import and Export Wizard that enable you to create and run DTS packages interactively.

You can use the DTS Import and Export Wizard to automatically create tables on SQL Server, and then copy data from Access to the new SQL Server tables. DTS can move data at a faster rate than the Microsoft Access Upsizing Wizard can, but DTS does not provide all of the features of the Access Upsizing Wizard. The following is a list of actions that the Access Upsizing Wizard can perform, but that the DTS Import and Export Wizard cannot perform:

• If an Access table has a primary key, the Access Upsizing Wizard automatically re-creates the primary key on the table that it generates on SQL Server; DTS does not.
• The Access Upsizing Wizard automatically migrates all rules and defaults that exist in a table to SQL Server; DTS does not.
• If tables in an Access database are related, the Upsizing Wizard automatically re-creates these relationships on SQL Server; DTS does not.
• DTS does not upsize any queries that exist in an Access database.

Using the DTS Import and Export Wizard

The following steps demonstrate how to use the DTS Import and Export Wizard to copy the Orders and Order Details tables from the sample database Northwind.mdb into a new SQL Server database.

Follow these steps to import the format of the .mdb or .accdb file on a computer that has SQL Server 2000 installed.

1. In Microsoft Windows XP and in earlier versions of Windows, click Start, point to Programs, point to Microsoft SQL Server, and then click Import and Export Data.
2. When the Data Transformation Services Wizard starts, click Next.
3. On the Choose a Data Source page, select Microsoft Access from the Data Source list.
4. Click the Build button (...) next to File Name box, and then browse to the sample database Northwind.mdb.
5. Double-click Northwind, and then click Next.
6. On the Choose a Destination page, select Microsoft OLE DB Provider for SQL Server from the Destination list.
7. In the Server list, click (local).
8. Click Use SQL Server Authentication, and then enter a user name and password recognized by SQL Server or MSDE as a user who has permissions to create databases and tables on the server.
9. Click Refresh, and then click <new> in the Database list.
10. In the Name box, enter MyNwind. Accept the default values in the Data file size and Log file size boxes, and then click OK.
11. Click Next twice.
12. In the Tables list, click the Order Details and Orders tables.
13. Click Next twice, and then click Finish.
14. The DTS package that you created with the DTS Import and Export Wizard runs. When the DTS package is finished, click OK, and then click Done to close the wizard.

Follow these steps to import the .accdb file format on a computer that has SQL Server 2005 installed.

1. In SQL Server Management Studio, connect to the Database Engine server type, expand Databases, right-click a DatabaseName, point to Tasks, and then click Import Data .
2. On the Choose a Data Source page, click Microsoft Office 12.0 Access Database Engine OLE DB Provider in the Data Source list, and then click Properties.
3. On the Data Link Properties page, type the DatabaseFile.accdb file name under Data Source, click OK, and then click Next.
4. On the Choose a Destination page, click Microsoft OLE DB Provider for SQL Server in the Destination list.
5. In the Server list, click ServerName, and then click Next.
6. On the Specify Table Copy or Query page, select Copy data from one or more tables or views, and then click Next.
7. On the Select Source Tables and Views page, select TableName to import the table, and then click Next.
8. On the Save and Execute Package page, click Next.
9. On the Complete the Wizard page, click Finish to close the wizard.