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:
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.
Read More
