ADO.NET Connection Manager in SSIS

ADO.NET Connection Manager in SQL Server Integration Services enables SSIS packages to connect with the Database using a 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 for accessing 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.
  • The OLE DB provider present in ADO.NET Connection Manager allows us to access the data sources OLE DB supports.
  • In real-time, the SqlClient data provider is the most commonly used .NET provider.

NOTE: Using the 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 create a new Project, 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.

Choose Add New Connection Option

When you click on the New Connection Manager, an Add Connection Manager window will open to select the connections managers from the list.

Choose the ADO.NET Connection Manager from SSIS List

Here, we selected the ADO.NET connection manager from the list. Once we choose the ADO.NET connection manager, the Configure ADO.NET Connection Manager window will open to configure the data connections.

ADO.NET Connection Manager in SSIS 4

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

ADO.NET Connection Manager in SSIS 5

From the above screenshot, you can observe that we selected the SqlClient Data Provider.

ADO.NET Connection Manager in SSIS 6

From the above image, we use 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 provided below to check whether the connection is successful or not.

Test the Connection

Once we finish configuring the connection managers, the SSIS ADO.NET connection manager’s name is displayed in the Data connection pane, so select the created connection manager and click ok.

ADO.NET Connection Manager in SSIS 8

Click the ok button to finish configuring the ADO.NET Connection Manager.

ADO.NET Connection Manager in SSIS 9

From the above screenshot, you can observe that We successfully created ADO.NET Connection Manager in SSIS.

Comments are closed.