Problem Statement
Many users report a strange behavior while using ZappySys Salesforce Source in SSIS:
Package works fine when executed from
- Visual Studio (SSDT)
- SSIS Catalog (SSISDB)
Package fails when executed via SQL Server Agent Job- Error seen:
System.Net.WebException: The operation has timed out
This article explains why this happens and the exact steps to fix it, based on a real-world support case.
Typical Error Message
You may see an error similar to this in the SQL Agent execution log:
[Salesforce Source] Error: System.Net.WebException: The operation has timed out
at ZappySys.Crm.SdfcPartnerApi.SforceService.query(String queryString)
Why This Happens
This issue is not related to Salesforce credentials being invalid.
It usually happens due to environment differences between:
- Interactive execution (Visual Studio / SSIS Catalog)
- Background execution (SQL Server Agent)
The most common root causes are:
- SQL Agent runs under a different service account
- Service account does not have internet / proxy access
- Large Salesforce datasets causing SOAP API timeouts
- Older ZappySys version without Bulk API support
- SSIS Package Protection Level misconfiguration
Solution Overview (What Actually Fixed It)
In this case, the issue was resolved using a combination of fixes, not just one setting.
Step 1: Verify SQL Agent Service Account Access
SQL Server Agent usually runs under:
NT SERVICE\SQLSERVERAGENT
or a custom domain account.
That account must be able to access Salesforce endpoints.
Test Connectivity (Run as SQL Agent Account)
Test-NetConnection login.salesforce.com -Port 443
If this fails, Salesforce API calls will timeout, even though they work in Visual Studio.
Recommended Fix
- Run SQL Agent Job using a Proxy Account
- OR change SQL Agent service to a domain account with internet access
- Ensure proxy/firewall rules allow outbound HTTPS traffic
Step 2: Configure SSIS Package Protection Level Correctly
Set the package ProtectionLevel to:
DoNotSaveSensitive
Then:
- Parameterize Password
- Parameterize Security Token
- Pass values via SSISDB Environment Variables
This avoids credential issues when the package is executed by SQL Agent.
Reference:
How to run an SSIS package with sensitive data on SQL Server
Step 3: Create a New SQL Agent Job (Important!)
This is often overlooked.
In this case:
- Older SQL Agent jobs did not show Catalog / Parameter mapping options
- Newly created jobs automatically picked up SSISDB parameters
Always create a new SQL Agent Job when:
- Switching to SSISDB
- Adding environments
- Parameterizing sensitive values
Step 4: Upgrade ZappySys PowerPack (Critical Fix)
The customer was using an older version (v3.3.0).
How to check the current version of SSIS PowerPack?
After upgrading to the latest ZappySys PowerPack, a new option became available:
Bulk API Mode
How to download FULL version (latest or older build)
Check the latest Release Notes here
Step 5: Enable Bulk API Mode in Salesforce Source
When loading large datasets, the Salesforce SOAP API can timeout.
Fix:
- Open ZappySys Salesforce Source
- Enable Bulk API Mode
- Optionally add a
LIMITclause in SOQL for testing
Example SOQL:
SELECT Id, Name FROM Account LIMIT 1000
After enabling Bulk API Mode, the package executed successfully:
- From Visual Studio
- From SSIS Catalog
- From SQL Agent Job
Final Resolution Summary
SQL Agent service account fixed
Package Protection Level set correctly
Parameters passed via SSISDB Environment
SQL Agent Job recreated
ZappySys upgraded to the latest version
Bulk API Mode enabled
Timeout issue resolved successfully
Key Takeaways
- If SSIS works in Visual Studio but fails in SQL Agent → always check service account
- Salesforce timeouts often mean network / proxy issues
- For large datasets, Bulk API Mode is strongly recommended
- Always upgrade to the latest ZappySys version before deep troubleshooting
Related Community Articles
How to fix OPENQUERY timeout when the preview works but the job fails