How to use SQL Server Data Transformation Services to upsize an Access database

This article details how to use SQL Server Data Transformation Services to upsize an Access database

Developers often use Access as a prototype or access to develop applications that are not critical. However, as the company's business grows, the problems to be solved become more complex and the access environment may not meet your needs. Currently, Access 2002 's. mdb and. adp files limit the length of a database to 2 GB. This means that almost every access and SQL Server developer will eventually have to upsize an Access database into a SQL Server database.

Because upsizing has become a very common task, Access provides a "Upsizing Wizard." Although it can complete this work, but the operation process is not simple. SQL Server is not compatible with access in several places that the wizard cannot handle. You may not know that SQL Server's Data Transformation Services (transformation Services,dts) can also upsize an Access database. Let's compare the two wizards and experience how to use DTS to upsize an actual Access database. As a result, you can choose the most effective way when you encounter a promotion task.

Compare Access Upsizing Wizard and DTS

The Access Upsizing Wizard (AUW) works inside access to copy data to a SQL Server table. Instead, DTS copies data from an Access database to the SQL Server table. Note that you can import data from a text file or from an OLE DB data source (which naturally includes an Access database) into SQL Server. When you import a file, you can also convert the data.

DTS is very full-featured, some developers think it is much better than Auw, the reason is:

You can change the column (field) properties when you import data.

Create queries during import to limit the data that is actually imported.

But Auw has one advantage that DTS does not have: it can link an access front-end to SQL Server data. DTS is otherwise, it can only import data, you cannot link the imported data to an existing front-end.