Use DTS to Migrate an Access Database to MSDE

Image 1 of 9

Use DTS to Migrate an Access Database to MSDE

With an aging homebrew helpdesk system increasingly experiencing speed and usability issues, database admins on a budget ($0) find a simple, affordable, and effective solution by using Microsoft Data Transformation Services (DTS) to migrate the backend Access database to Microsoft Data Engine (MSDE). This gallery shows you the migration process.
Select DTS

This screenshot shows the selection of DTS as the data migration tool. You will go to Tools -> Data Transformation Services -> Import Data in SQL Enterprise manager to access the DTS Wizard.
Select Data Source

In the first screen, you will select the source you want to import your data from. For this exercise, we will be converting from MS Access to SQL so we will select Microsoft Access as the Data Source.
Create New SQL Database

You may need to import your data into a new SQL database. You can create a new database within the wizard by selecting NEW from the database dropdown list.
Select Data to Copy

Within the wizard you will be prompted for the data to copy. You can select all by using the Select All button, or manually check the tables. You can also change the destination tables of the data, or specify specific transformations of the data.
Prepare the Copy

You will be offered the offered the choice of using the GUI tool to select the data you wish to transform, or you can use a T-SQL query to specify the data transformation.
Run or Schedule

You will then be prompted to Run the transformation, schedule it to run later, save the package to be executed later, or a combination of all of these.
Run DTS

While DTS is running you will be able to view the tables that are completed, and the amount of data that has been transformed in each.
Tables in SQL

After the wizard is complete, you will be able to view your new tables via SQL Enterprise Manager, or by any other method of D.B. access you use.

Use DTS to Migrate an Access Database to MSDE

With an aging homebrew helpdesk system increasingly experiencing speed and usability issues, database admins on a budget ($0) find a simple, affordable, and effective solution by using Microsoft Data Transformation Services (DTS) to migrate the backend Access database to Microsoft Data Engine (MSDE). This gallery shows you the migration process.

Use DTS to Migrate an Access Database to MSDE

Select DTS

Select Data Source

Create New SQL Database

Select Data to Copy

Prepare the Copy

Run or Schedule

Run DTS

Tables in SQL

Use DTS to Migrate an Access Database to MSDE

Join Discussion