Export (0) Print
Expand All
Expand Minimize
4 out of 11 rated this helpful - Rate this topic

Connect to an Access Database

To connect an Integration Services package to a Microsoft Office Access data source requires an OLE DB connection manager and a data provider. The data provider that you use depends on the version of Access that created the data source:

  • For Access 2003 and earlier, the package requires the Microsoft Jet OLE DB Provider.

  • For Access 2007, the package requires the OLE DB provider for the Microsoft Office 12.0 Access Database Engine.

You can create an OLE DB connection manager and select the corresponding data provider from either the Connection Managers area in SSIS Designer or from the SQL Server Import and Export Wizard.

Note Note

On a 64-bit computer, you must run packages that connect to Microsoft Access data sources in 32-bit mode. Both the Microsoft Jet OLE DB Provider and the OLE DB provider for the Microsoft Office 12.0 Access Database Engine are only available in 32-bit versions.

To create an Access connection manager from the Connection Managers area

  1. In SQL Server Data Tools (SSDT), open the package.

  2. In the Connections Managers area, right-click anywhere in the area, and then select New OLE DB Connection.

  3. In the Configure OLE DB Connection Manager dialog box, click New.

    For more information, see OLE DB Connection Manager.

  4. In the Connection Manager dialog box, for Provider, select Microsoft Jet 4.0 OLE DB Provider, and then configure the connection manager as appropriate.

To create an Access connection from the SQL Server Import and Export Wizard

  1. In SQL Server Data Tools (SSDT), start the SQL Server Import and Export Wizard.

  2. On the Choose a Data Source page, for Data Source, select Microsoft Access, and then configure the Access connection.

    When you select Microsoft Access as the Data Source, the wizard automatically creates the necessary OLE DB connection manager with the correct data provider. For more information, see OLE DB Connection Manager.

To access an Access 2007 data source, the OLE DB connection manager requires the OLE DB provider for the Microsoft Office 12.0 Access Database Engine. This provider is installed automatically with the 2007 Microsoft Office system. If the 2007 Office system is not installed on the computer on which Integration Services is running, you have to install the provider separately. To install the OLE DB provider for the Microsoft Office 12.0 Access Database Engine, download and install the components on this Web page, 2007 Office System Driver: Data Connectivity Components.

To create an OLE DB connection manager from the Connection Managers area

  1. In SQL Server Data Tools (SSDT), open the package.

  2. In the Connections Managers area, right-click anywhere in the area, and then select New OLE DB Connection.

  3. In the Configure OLE DB Connection Manager dialog box, click New.

    For more information, see OLE DB Connection Manager.

  4. In the Connection Manager dialog box, for Provider, select Microsoft Office 12.0 Access Database Engine OLE DB, and then configure the connection manager as appropriate.

    Note Note

    To connect to data source that uses Access 2007, you cannot select Microsoft Jet 4.0 OLE DB Provider as the Data Source.

To create an OLE DB connection from the SQL Server Import and Export Wizard

  1. In SQL Server Data Tools (SSDT), start the SQL Server Import and Export Wizard.

  2. On the Choose a Data Source page, for Data Source, select Microsoft Office 12.0 Access Database Engine OLE DB Provider, and then configure the connection as appropriate.

    Note Note

    To connect to data source that uses Access 2007, you cannot select Microsoft Jet 4.0 OLE DB Provider as the Data Source.

    When you select Microsoft Office 12.0 Access Database Engine OLE DB Provider as the Data Source, the wizard automatically creates the necessary OLE DB connection manager with the correct data provider. For more information, see OLE DB Connection Manager.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.