ADO.NET Connection Manager in SQL Server Integration Services enables SSIS packages to connect with Database using managed .NET provider. For example, an ADO.NET connection manager uses SqlClient data provider to connect with Microsoft SQL Server. Following are the some of the important properties of the ADO.NET Connection Manager:
- ADO.NET Connection Manager uses a managed .NET provider to access the Database.
- This Connection Manager is very useful to access the data source written in managed code.
- ADO.NET Connection Manager in SSIS is normally used to access Microsoft SQL Server
- SSIS ADO.NET Connection Manager Supports many managed provides 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, 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 new Project under SSIS. If you look at the Solution explorer we have three folders. For now lets 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 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 ADO.NET connection manager from the list. Once we selected the ADO.NET connection manager then another window form of Configure ADO.NET 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 selected the SqlClient Data Provider.
From the above screenshot you can observe 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 ADO.NET 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 ADO.NET Connection Manger.
From the above screenshot you can observe that, We successfully created ADO.NET Connection Manager in SSIS.
Thank You for Visiting Our Blog