SSIS Script Task and Proxy Auto Configuration (PAC) Script

When you need to import data from an external source, using JavaScript Object Notation [JSON], through Microsoft SQL Server Integration Services [SSIS], you can do so by using a Script Task to set up the connection, retrieve the HttpWebRequest and pass the data along to SQL Server. In some corporate setups, you could encounter a Proxy Auto-Configuration [PAC] Script, which can complicate things a bit. A PAC file defines how any user-agent ( such as a web browser )can choose the appropriate proxy server to use in order to fetch a given URL.  The example PAC script from that Wiki link is:

function FindProxyForURL(url, host) {
 // our local URLs from the domains below example.com don't need a proxy:
 if (shExpMatch(host, "*.example.com"))
 {
   return "DIRECT";
 }
 
 // URLs within this network are accessed through
 // port 8080 on fastproxy.example.com:
 if (isInNet(host, "10.0.0.0", "255.255.248.0"))
 {
   return "PROXY fastproxy.example.com:8080";
 }
 
 // All other requests go through port 8080 of proxy.example.com.
 // should that fail to respond, go directly to the WWW:
 return "PROXY proxy.example.com:8080; DIRECT";
}

The quick reader, will already have spotted, that such a PAC Script can indeed return several proxy URLs, separated by a semicolon ';'.
This can easily catch out the developer new to the concept.
A good article explaining how to integrate this into SSIS is:
http://www.codeproject.com/Articles/12168/Using-PAC-files-proxy
The article explains how to set a proxy using PAC files. The solution presented in the article uses the WinHttp.dll for obtaining a proxy URL. 

HttpWebRequest request = null;HttpWebResponse response = null; string listOfServers = GetProxyForUrlUsingPac(Variables.SourceURL, "http://www.yourdomain.com/pac/pacscriptname.pac");WebProxy p = new WebProxy("http://" + listOfServers.Split(new char[] { ';' })[0]);request = (HttpWebRequest)WebRequest.Create(Variables.SourceURL);request.Proxy = p;response = (HttpWebResponse)request.GetResponse(); Stream s = response.GetResponseStream();StreamReader sr = new StreamReader(s);string json = sr.ReadToEnd();//DO Whatever you want with the JSON stream
This code will work fine so long as that first server is available.  In a production script, you might want to make use of the list of servers rather than just the first.