Share via

  • Article

Task 3: Adding and Configuring an OLE DB Connection Manager

After you have added a Flat File connection manager to connect to the data source, the next task is to add an OLE DB connection manager to connect to the destination. An OLE DB connection manager enables a package to extract data from or load data into any OLE DB–compliant data source. Using the OLE DB Connection manager, you can specify the server, the authentication method, and the default database for the connection.

In this lesson, you will create an OLE DB connection manager that uses Windows Authentication to connect to the local instance of AdventureWorksDB. The OLE DB connection manager that you create will also be referenced by other components that you will create later in this tutorial, such as the Lookup transformation and the OLE DB destination.

To add and configure an OLE DB Connection Manager

  1. Right-click anywhere in the Connection Managers area, and then click New OLE DB Connection.

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

  3. For Server name, enter localhost.

    When you specify localhost as the server name, the connection manager connects to the default instance of SQL Server on the local computer. To use a remote instance of SQL Server, replace localhost with the name of the server to which you want to connect.

  4. In the Log on to the server group, verify that Use Windows Authentication is selected.

  5. In the Connect to a database group, in the Select or enter a database name box, type or select AdventureWorksDW.

  6. Click Test Connection to verify that the connection settings you have specified are valid.

  7. Click OK.

  8. Click OK.

  9. In the Data Connections pane of the Configure OLE DB Connection Manager dialog box, verify that localhost.AdventureWorksDW is selected.

  10. Click OK.

Next Task in Lesson

Task 4: Adding a Data Flow Task to the Package

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.

See Also

Concepts

OLE DB Connection Manager