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), we can use this SSIS 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. If you look at Solution Explorer, we have four folders. For now, let’s concentrate on the Connection Managers Folder.
Right-click on the SSIS 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 open to select the connections managers from the provided list.
Here, we selected the ADO connection manager from the list. Once we chose the ADO connection manager, another Configure OLEDB Connection Manager window opened. Use this to configure the data connections for the SSIS ADO connection manager.
If you observe the above, the Data Connections pane is not empty. Because we already created a few connections before, that’s why they are displaying. If you create the ADO Connection Manager for the first time, the Data Connections pane will be empty. If you want, you can select the connection manager from the list. Or you can create a New ADO Connection Manager by clicking 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 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 SSIS ADO Connection Manager 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. 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’s name is displayed in the Data connection pane. Select the created connection manager and click ok.
Click the ok button to finish configuring the SSIS ADO Connection Manager.
From the above screenshot, you can observe that We successfully created ADO Connection Manager in SSIS.
Comments are closed.