Step 3: Adding and Configuring an OLE DB Connection Manager
Applies To: SQL Server 2016
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 AdventureWorksDB2012. 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.
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 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.
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 AdventureWorksDW2012.
Click Test Connection to verify that the connection settings you have specified are valid.
In the Data Connections pane of the Configure OLE DB Connection Manager dialog box, verify that localhost.AdventureWorksDW2012 is selected.