CONFIGURING AN SSIS OLE DB CONNECTION MANAGER

An OLE DB connection manager enables a package to extract data from or load data into any OLE DB-compliant data source.

Add and configure an OLE DB connection manager

1. To do this, we first of all download and install the AdventureWorksDW2012 dataset

2. Copy the link C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup, go to any location in your windows and paste it.

3. It will navigate you straight to where the databases are kept.

4. Copy the downloaded data set and paste it in the location of the databases.

5. Open your Microsoft SQL server management studio

6. Right-click Databases in Object Explorer > Restore Database… to launch the Restore Database wizard.

7. Select Device and then select the ellipses (…) to choose a device.

8. Select Add and then choose the .bak file you recently moved to the backup location. If you moved your file to this location but you’re not able to see it in the wizard, SQL Server or the user signed into SQL Server doesn’t have permission to this file in this folder.

9. Select OK to confirm your database backup selection and close the Select backup devices window.

10. Check the Files tab to confirm the Restore as location and file names match your intended location and file names in the Restore Database wizard.
11. Select OK to restore your database.

12. Go back to object explorer and click on database. You will see that it will expand, showing all the databases. 

13. In the Add SSIS Connection Manager dialog, select OLEDB, then select Add.

14. In the Configure OLE DB Connection Manager dialog box, select New.

15. 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.

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

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

18. Select Test Connection to verify that the connection settings you have specified are valid.

19. Select OK.

20. Select OK.

21. In the Connection Managers pane, verify that localhost.AdventureWorksDW2012 is selected.
22. Now we have the local host connection manager