Fix: Azure / MS SQL Server Connection Not Showing in ZappySys SSIS Upsert Destination

[pankaj]

When using the ZappySys SSIS Upsert Destination component, you may encounter a situation where the Azure SQL Server / MS SQL Server (ADO.NET) Connection is not visible in the connection drop-down list. This typically happens when the connection is not configured properly or was created using an unsupported provider type.

---

Root Cause

The Upsert Destination Component in ZappySys requires an ADO.NET Connection configured specifically with the SqlClient Data Provider. If a different provider type or incorrect settings are used, the connection will not appear in the component’s list.

---

Resolution Steps

To ensure the MS SQL Server connection appears correctly, follow these steps:

1. Create a New ADO.NET SQL Connection

   • In your Upsert Destination select New ADO.NET SQL Connection.
     
     

2. In the Connection Manager dialog

   • Click on the New button to create a new connection.
     
     

3. Select the Correct Provider

   • Choose .NET Providers\SqlClient Data Provider.
   • This is required for the Upsert Destination Component to detect the SQL Server connection.
     
     

4. Enter Server Details

   • For Azure SQL Server, use the following format:

     tcp:yourazure.database.windows.net,1433
     

     Replace yourazure with your actual Azure SQL Server name.

   • For MS SQL Server (on-premises), you can use:

     YourServerName\InstanceName
     

     or

     YourServerIPAddress,1433
     

5. Provide Authentication Information

   • Enter the following:
     • Database Name: e.g., YourDatabaseName
     • User Name and Password

6. Configure Other Options - depending on your SQL Server configuration, set:

   • Encrypt: True / False (Optional)
   • TrustServerCertificate : True / False (Optional)
   • Save my password: True

7. Test the Connection - Click Test Connection to ensure the connection is successful.

8. Save and use the Connection

   • Once the test passes, save the connection.
     

     

   • It will now be visible and selectable in the ZappySys Upsert Destination Component.
     

     

---

Additional Tips

• Always verify that the ZappySys PowerPack and SQL Server client libraries are up to date.
• If the connection still doesn’t appear, restart Visual Studio or SSDT after creating the new ADO.NET connection.
• Avoid using OLE DB or ODBC connections, as they are not compatible with the Upsert Destination for SQL Server.

Reference Links

• ZappySys SSIS Upsert Destination
• ZappySys SSIS PowerPack