The SMO (SQL Server Management Objects) Connection Manager establishes a connection with the SQL Management Object server. In Integration Services, all the Database related tasks use SMO Connection Manager to connect with SQL server objects.
For example, Transfer SQL Server Objects Task uses SMO Connection Manager to connect with the server.
Configuring SMO Connection Manager in SSIS
In this example, We are going to show, How to create or configure SMO Connection Manager in SQL Server Integration Services (SSIS)
Once you create a new Project, if you look at the Solution explorer, we have three folders. For now, let us 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 Connection Manager window form will open to select the connections managers from the list.
Here we selected the SMOServer connection manager from the list. Once we selected the SMO connection manager, then another window of SMO Connection Manager Editor opened to configure the connection.
The SSIS SMO Connection Manager provides the following options:
- Server Name: Please specify the SQL Server instance name
- Refresh: Clicking on this button will refresh the SQL Server instances available in the Network.
- Use Windows Authentication: If we select this option, SMO Connection Manager will connect with the SQL Server instance using Windows Authentication. If you are practicing or learning at home, then choose this option. In real-time, we mostly don’t use this option.
- Use SQL Server Authentication: If we select this option, SSIS SMO Connection Manager will connect with the SQL Server instance using SQL Server Authentication. In real-time, this is the option we use. If you selected this option, provide the User name and Password.
- User Name: Please provide the user name to access the SQL Server. Your organization will provide these credentials.
- Password: Please provide the password to access the SQL Server.
- Test Connection: Click on this button to test our connection.
From the above screenshot, you can observe that we selected Windows Authentication to connect with the SQL Server instance (SURESH). In real-time, you have to select the Use SQL Server Authentication option and provide the valid credentials given by your Admin person. So let us provide the same here to show you how it works.
Click on the Test Connection button provided below to check whether the connection is successful or not.
Click the ok button to finish configuring the SQL Server Management Object Connection Manager.
From the screenshot above, we successfully created SMO Connection Manager in SSIS.