The SMO (SQL Server Management Objects) Connection Manager is used to establish connection with SQL Management Object server. In SQL Server Integration Services, all the Database related tasks uses SMO Connection Manager to connect with SQL server objects. For example, Transfer SQL Server Objects Task in SSIS 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 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 SMOServer connection manager from the list. Once we selected the SMO connection manager then another window form of SMO Connection Manager Editor will be opened to configure the connection.
SMO Connection Manger provides 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 then, SMO Connection Manager will connect with the SQL Server instance using Windows Authentication. If you are practicing or learning at home then select this option. In real-time we mostly don’t use this option.
- Use SQL Server Authentication: If we select this option then, 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 then you have to 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 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 ok button to finish configuring the SQL Server Management Object Connection Manger.
From the above screenshot you can observe that, We successfully created SMO Connection Manager in SSIS.
Thank You for Visiting Our Blog