The OLE DB Connection Manager in SSIS enables SQL Server Integration Services packages to connect with the Database using an OLE DB provider. For example, an OLE DB connection manager uses Microsoft OLE DB Provider for SQL Server to connect with Microsoft SQL Server.
In real-time, the OLE DB Connection Manager is the most used connection manager while designing the SSIS packages. The following are some of the occasions we use:
- OLE DB Source uses OLE DB Connection Manager to connect and extract data from the Database in SSIS
- OLE DB Destination uses the OLE DB Connection Manager to connect and write data into the Database
- Term Lookup Transformation only supports OLE DB Connection Manager to connect with the term lookup table (or Reference table).
- Term Extraction Transformation only supports the SSIS OLE DB Connection Manager to add the Exclusion List.
- Execute SQL task uses OLE DB Connection Manager to connect and extract data from the Database.
- The OLE DB Connection Manager is also helpful in accessing data from unmanaged code.
Configuring OLE DB Connection Manager in SSIS
In this example, We are going to show How to create or configure OLE DB Connection Managers in SSIS (SQL Server Integration Services)
Once you create a new Project, if you look at the Solution Explorer, we have four folders. For now, let’s concentrate on the Connection Managers Folder.
Within the Solution Explorer, Right-click on the Connection Managers and select the New Connection Manager option from the context menu.
Here we selected the OLE DB connection manager from the list. Once we select the OLE DB connection manager, another window form of Configure OLE DB Connection Manager will be opened to configure the data connections.
If you observe the above, the Data Connections pane is empty because we have not created any connection managers before. If we created any connection managers before then, instead of creating them repeatedly, we could select them here.
Click on the new button from the above screenshot. It will open another window form of Connection Manager to select the Provider, Server Name, and Database Name.
From the above screenshot, you can observe that we are using our local host Windows account as the server name and [AdventureWorks2014] as the database name.
NOTE: You must select the Use SQL Server Authentication option in real time. And provide the valid credentials given by your Admin person.
Click on the Test Connection button below to check whether the connection is successful.
Once we finish configuring the connection managers, the OLE DB connection manager’s name will be displayed in the Data connection pane. So, select the created connection manager and click ok.
Click the ok button to finish configuring the OLE DB Connection Manager.
From the above screenshot, you can observe that We successfully created the OLE DB Connection Manager in SSIS on SQL Server.