The ADO Connection Manager in SSIS enables SQL Server Integration Services packages to connect with ActiveX Data Objects (In short ADO Objects). For example, if we want to connect with the data source written in previous versions (VB 6) then we can use this ADO Connection Manager.
Configuring ADO Connection Manager in SSIS
In this example, We are going to show, How to create or configure ADO 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 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 connection manager from the list. Once we selected the ADO connection manager, another window form of Configure OLEDB Connection Manager will be opened. Use this to configure the data connections for ADO connection manager.
If you observe the above, Data Connections pane is not empty. Because we already created a few connections before that’s why they are displaying. If you are creating the ADO Connection Manager for the first time then Data Connections pane will be empty. If you want you can select the connection manager from the list, or else you can create New ADO Connection Manager by clicking on the New button.
Click on the new button from the above screenshot, it will open another window form of Connection Manager. Use this window to select the Provider, Server Name and Database Name.
In this example, we are connecting with SQL Server so let us select the SQL Server Native Client from the Provider drop-down list.
Please provide the SQL Server Instance Name and then select the Database from the drop-down list.
From the above screenshot, you can observe that we are using our windows account as the server name. And [TRANSFERDBTASKS] as the database name.
NOTE: In real-time you have to select the Use SQL Server Authentication option. And also 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 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 Connection Manger.
From the above screenshot, you can observe that We successfully created ADO Connection Manager in SSIS.
Thank you for Visiting Our Blog