The OLE DB Connection Manager in SSIS enables SQL Server Integration Services packages to connect with 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, OLE DB Connection Manager is the most used connection manager while designing the SSIS packages. Following are the some of the occasions we use:
- OLE DB source in SSIS use OLE DB Connection Manager to connect and extract data from the Database
- OLE DB Destination use OLE DB Connection Manager to connect and write data into the Database
- Term Lookup Transformation only support OLE DB Connection Manager to connect with the term lookup table (or Reference table).
- Term Extraction Transformation only supports OLE DB Connection Manager to add the Exclusion List.
- Execute SQL task use OLE DB Connection Manager to connect and extract data from the Database
- OLE DB Connection Manager is also helpful to access data from the 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 created a new Project under SSIS. If you look at the Solution Explorer we have four folders. For now, let’s concentrate on the Connection Managers Folder.
Right click on the Connection Managers folder present in the solution explorer. Next, select the New Connection Manager option from the context menu.
When you click on the New Connection Manager option, an Add SSIS Connection Manager window form will be opened to select the connections managers from the list it is provided.
Here we selected the OLE DB connection manager from the list. Once we selected the OLE DB connection manager then another window form of Configure OLE DB Connection Manager will be opened to configure the data connections.
If you observe the above, 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, again and again, we can 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: In real-time you have to select the Use SQL Server Authentication option. And provide the valid credentials given by your Admin person.
Click on the Test Connection button provided below to check whether the connection is successful or not.
Once we finish configuring the connection managers, the OLE DB connection manager name will be displayed in the Data connection pane. So select the created connection manager and click ok.
Click ok button to finish configuring the OLE DB Connection Manager.
From the above screenshot, you can observe that We successfully created OLE DB Connection Manager in SSIS on SQL Server.
Thank you for Visiting Our Blog