Network causing SSIS package startup delays?
This issue is covered pretty well by blogs in the context of SSMS (Dan Jones and Euan Garden) and in the context of SSIS service it has a KB article. But I still see forums posts and private questions that show that not all SSIS users are aware of it or realize that the same issue can cause SSIS package execution delays, especially when running as an Agent Job or another non-interactive service.
The issue in short: managed SQL Server assemblies are signed with Microsoft certificate, and when the application starts, .NET validates this key, including checking CRL (certificate revocation list - remember when Verisign issued two "Microsoft" certificates to unknown party? - CRL helps mitigate the harm from such mistakes). CryptoAPI tries to retrieve CRL using HTTP request (in case of SQL certificate - to crl.microsoft.com), and usually everything is fine. Even if this site is not accessible (e.g. you don't have internet connection at all), it is not a problem: CryptoAPI gets an error from network, and simply ignores the CRL.
When does it cause a problem? If the network (or firewall) does not reply with the error code telling CryptoAPI that the site is not accessible, but silently drops the packets. This causes the certificate check to timeout after 15 seconds. If multiple certificates are involved, the delays add up, e.g. 30 seconds in case of two certificates.
Unfortunately, the fix for this issue is only in upcoming .NET 3.5. For now, you have to configure the network and firewall to behave correctly, i.e. either allow access to crl.microsoft.com, or quickly report the error.
Why did I write in the beginning that this affects execution via Agent more often than interactive users? This is caused by firewalls more often working correctly for interactive and domain users than for non-interactive or local users. Some firewalls work correctly when proxy settings are configured (manually or often using logon scripts), so everything is fine for interactive users. But the settings for Agent service user account or Agent Proxy user account might not be configured properly, which causes the delays when the job is executed. So if your packages takes 5 seconds when running interactively, and 35 when run as a job, you now know why.
