SSIS Add and Configure an OLE DB

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

After adding a Flat File connection manager to connect to the data source, we now need to add an OLE DB connection manager to connect to the data destination.

  • An OLE DB connection manager enables a package to extract data from or load data into any OLE DB-compliant data source.
  • Using an OLE DB connection manager, you can specify the server, the authentication method, and the connection's default database.

Add and configure an OLE DB connection manager

In the Solution Explorer pane, right-click on Connection Managers and select New Connection Manager.

image

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

image

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

image

For Server name, enter localhost or any other server name which is created during SQL Server installation.

image

When you specify 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, make sure that Windows Authentication is selected.

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

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

image

Select OK.

image

Select OK.

image

In the Connection Managers pane, verify that *.AdventureWorksDW2012 is Added.



Got any SSIS Question?