SSIS Salesforce Connection Failing in SQL Agent Job but Works in Visual Studio

SSIS PowerPack
,
1

Problem Statement

We observed a frustrating issue where an SSIS package using the ZappySys Salesforce connector would run perfectly when executed from Visual Studio (developer environment) or via the SSIS Catalog (SSISDB), but would fail when scheduled via a SQL Server Agent Job.

The error that appeared when run under SQL Agent typically looked like this:

System.Net.WebException: The operation has timed out
at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
…
at ZappySys.Crm.SdfcPartnerApi.SforceService.query(String queryString)
Interestingly, in Visual Studio or when run manually, the Test Connection always succeeded, and one of the interfaces even worked in VS, whereas others failed under the Agent.

Investigation & What We Tried

We systematically went through multiple common causes:

  1. Package Protection Level / Sensitive Data Handling

    • Tried DoNotSaveSensitive and parameterizing credentials (username, password + security token)

    • Tried EncryptSensitiveWithPassword / EncryptSensitiveWithUserKey variants

    • Verified that the parameters were being properly passed at runtime

  2. Job / Agent Differences

    • Created new SQL Agent Jobs (to rule out misconfigurations)

    • Linked the jobs to SSISDB environments and mapped parameters

    • Ensured variables in SSIS environments were properly set

  3. Connection Context / Runtime Account

    • Checked which account the SQL Agent service was running under

    • Verified that account’s network/firewall access to Salesforce endpoints

    • Compared that to permissions accessible under the interactive user account

Everything seemed correct — packages and connections worked in VS or SSISDB, parameter values were correct — but under the Agent, the connection would either timeout or not pick up the credentials correctly.