ADO.NET Connection Manager in SQL Server Integration Services enables SSIS packages to connect with Database using managed .NET provider. For example, an SSIS ADO.NET connection manager uses the SqlClient data provider to connect with Microsoft SQL Server. Following are some of the crucial properties of the ADO.NET Connection Manager:
- The SSIS ADO.NET Connection Manager uses a managed .NET provider to access the Database.
- This Connection Manager is useful to access the data source written in managed code.
- ADO.NET Connection Manager in SSIS is typically used to access Microsoft SQL Server
- SSIS ADO.NET Connection Manager Supports many managed providers such as SqlClient, ODBC, and OLE DB.
- OLE DB provider present in ADO.NET Connection Manager allows us to access the data sources supported by OLE DB.
- In real-time, the SqlClient data provider is the most commonly used .NET providers.
NOTE: Using OLE DB provider in ADO.NET Connection Manager will affect the performance because it has to add extra managed larger on the OLE DB (Unmanaged provider).
Configuring ADO.NET Connection Manager in SSIS
In this example, we are going to show, How to create or configure ADO.NET 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 three folders. For now, let’s concentrate on the Connection Managers Folder.
Right-click on the Connection Managers folder present in the solution explorer and select the New Connection Manager option from the context menu.
When you click on the New Connection Manager, an Add SSIS Connection Manager window will open to select the connections managers from the list.
Here we selected the ADO.NET connection manager from the list. Once we chose the ADO.NET connection manager, the Configure ADO.NET Connection Manager window will open 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 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 selected the SqlClient Data Provider.
From the above image, see that we are using our local host windows account as 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 SSIS ADO.NET connection manager’s name displayed in the Data connection pane, so select the created connection manager and click ok.
Click the ok button to finish configuring the ADO.NET Connection Manager.
From the above screenshot, you can observe that We successfully created ADO.NET Connection Manager in SSIS.