Tip: Importing Microsoft Access 2007 Database Tables into SQL Server

Microsoft SQL Server 2005 and 2008 offer the Import and Export Wizard to move data to and from an external source. You can also create a basic SQL Server Integration Services (SSIS) package which can be used later in the Business Intelligence Development Project.

With the Import and Export wizard, you can access different types of data sources. These sources include database formats such as Microsoft Access, Microsoft SQL Server, flat files, Microsoft Excel, and Oracle. This article discusses importing Access 2007 database tables into MS SQL Server 2005/2008.

Importing Microsoft Access MDB databases (2003-format or earlier) is a built in feature of SQL Server. However, because of the difference between the database engine of Microsoft Access 2007 and earlier versions, it is not possible to connect to the Access 2007 database (*.ACCDB) using the built-in data source “Microsoft Access”. To import data from a Microsoft Access 2007 database, you must install the OLEDB Provider for Microsoft Office 12.0 Access Database Engine. Refer to the following FMS tip for details:

Errors Importing Data from a Microsoft Access 2007 Format (*.ACCDB) File into SQL Server

After installing this driver, open SQL Server Management Studio and connect to the desired instance of SQL Server database engine. In the Object Explorer, it shows database list available in that instance of SQL Server. Select a desired database or create a new one. Right Click this database and select Tasks -> Import Data.

Now follow the several pages of the wizard. The steps below detail how to import data into a SQL Server 2005 database, but the steps are very similar in SQL Server 2008.
On the first page, select the Data source from which you want to import the data. There are several data sources also available such as:

• Microsoft OLEDB provider for SQL Server
• Microsoft OLEDB provider for Oracle
• SQL Native Client
• Microsoft Access
• Microsoft Excel, etc…

Because of the difference between the database engine of Microsoft Access 2007 and earlier version of Microsoft Access, it is not possible to connect to the Access 2007 database using data source “Microsoft Access”. You can use this if you wish to import data from a MDB format, but not an ACCDB from Access 2007.
If you have properly installed the 2007 Office System driver, you will see another Data Source option: “Microsoft Office 12.0 Access Database Engine.”

Select this option to import from an ACCDB file. Then click the Properties button to open the Data Link Properties window:

On the Data Link Properties page, provide the location of database in to the Data Source field, and enter the User name and password if applicable. Click on the Test Connection and make sure it succeeds.
Click OK to close the Data Link Properties sheet, and continue to the next page of the Wizard, to choose the Destination.

Your destination is SQL Native Client, since you want to import to SQL Server. The server name and database should already contain the database that you want to import to.
Click Next to continue, and choose option for Copy data from one or more tables or views.

Click Next, and select the Source Tables or Views to import. If necessary use the Edit Mapping button to map the Columns correctly.

The next screen of the wizard shows two options. First one is to Execute Package Immediately and second one is to save SSIS package which can be used in the Business Intelligence Development Studio Project.

Click Next and Finish the Wizard. It shows all successful action list and errors if any.

Once execution is finished, you can click on the database and see the imported tables from Access database.

This way you can import data from Access 2007 database tables and also create an SSIS package for a Business Intelligence Development Project.
The limitation of the wizard is it doesn’t share sources with other packages and more advanced logic can’t be applied in the wizard.

Note About Attachment and MultiValue Fields

Access 2007 ACCDB files support Complex field types: Attachment fields and MultiValue fields. When you import such fields, they're imported as Long Text fields.

• Values from a MultiValue fields are imported to a single Long Text field, with values separated by a semicolon (e.g. Value1;Value2;Value3).
• Values from an Attachment field are stored similarly, except only the name of the Attachment is stored, not the attachment itself (e.g. Picture1;TextFile1)

Resource: http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/importing/Import_Access_DB.asp