Pfthb

I am trying to write an SSIS package that connect to an XML feed to download FX rates and import them into an SQL table.

i have the code below, but cannot figure out how to implement logging in through a proxy.

if anyone can think of an easier way to do the above, i'm more than willing to give it a try.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml
Imports System.Net



Public Class ScriptMain

Public Sub Main()


 Dim xmlDoc As XmlDataDocument, strXML As String
 Dim client As System.Net.WebClient = New System.Net.WebClient()

 Dim cr As New System.Net.NetworkCredential("user", "password")
 Dim pr As New System.Net.WebProxy("proxy", 8080)

 pr.Credentials = cr
 client.Proxy = pr

 xmlDoc = New XmlDataDocument
 xmlDoc.Load("http://themoneyconverter.com/GBP/rss.xml")
 strXML = CType(xmlDoc.InnerXml, String)
 Dts.Variables("strXMLData").Value = strXML
 Dts.TaskResult = Dts.Results.Success
End Sub

End Class

Many thanks.

You can use the credentials of the currrent login with:

Imports System.Net

Dim Pr As New System.Net.WebProxy(Proxy, Port)
Pr.Credentials = System.Net.CredentialCache.DefaultCredentials
WebRequest.DefaultWebProxy = Pr

' WebRequest.DefaultWebProxy is the proxy settings used by xml.Load()

Worked perfectly in SSIS 2012 - thanks

Aside from the Imports System.Net in the declaration, my final code looked like this :

 Dim Proxy As String, Port As Integer

 Proxy = "EnterYourProxyServerName" 
 Port = TypeInYourProxyPortAsANumber

 Dim Pr As New System.Net.WebProxy(Proxy, Port) 
 Pr.Credentials = System.Net.CredentialCache.DefaultCredentials
 WebRequest.DefaultWebProxy = Pr 
 Dts.TaskResult = ScriptResults.Success

Obviously this is a client side proxy hack.

Haven't tried this on the server yet.