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
Right-click anywhere in the Connection Managers area, and then click New OLE DB Connection.
In the Configure OLE DB Connection Manager dialog box, click New.
For Server name, enter localhost.
When you specify localhost as the server name, the connection manager connects to the default instance of Microsoft SQL Server 2005 on the local computer. To use a remote instance of SQL Server 2005, replace localhost with the name of the server to which you want to connect.
In the Log on to the server group, verify that Use Windows Authentication is selected.
In the Connect to a database group, in the Select or enter a database name box, type or select AdventureWorksDW.
Click Test Connection to verify that the connection settings you have specified are valid.
Click OK.
Click OK.
In the Data Connections pane of the Configure OLE DB Connection Manager dialog box, verify that localhost.AdventureWorksDW is selected.
Click OK.
Next Task in Lesson
Adding a Data Flow Task to the Package