March 7, 2012 at 6:10 pm
Hi there,
I've been given a request to connect to a HTTPS URL to download a CSV file. Before I could connect to the specific URL I needed to install a certificate that the vendor provided. The URL I'm using looks something like the following (I think that means it's a Web Service?):
https://test.testing.org.nz/reports/Export/CSVExporter.aspx?StartDate=2011-12-01&UserID=0000
So I can do this in Internet Explorer well enough however the download needs to be automated and run each day. As you might be able to guess the StartDate parameter in the URL needs to be modified each day as well so we only get the most recent data.
I figured I might be able to do something like this with SSIS as eventually the CSV file needs to go into a database.
I've tried the HTTP Connection Manager however this does not seem to work, when I put in the Server URL (as shown above with slight changes), ticked the box to use a certificate and then specified that I want to use a proxy server supplying credentials when I press "Test Connection" I get the error "The underlying connection was closed: An unexpected error occurred on a receive.".
So reading that I'd figured well it did say HTTP Connection Manager and not HTTPS Connection Manager, I guess it does not work for HTTPS. I was wanting to use the Web Service Task however it needs an HTTP Connection Manager so I guess that was ruled out.
Next I figure that I'd have to write a VB.NET script and use the WebClient class. After a couple of days searching about I can't find any examples which help me. I've written code to connect to a HTTP web site and download a file which works (shown below) but I don't know how to do the following:
1. Connect to an HTTPS web site.
2. Pass a certificate for authentication.
3. Use the web service (I think it's a web service, sorry my web knowledge it a little off) to download the file to a specified location.
4. Should the proxy be required pass the proxy information.
Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dim webConnection As New WebClient
Try
With webConnection
.BaseAddress = "http://www.google.co.nz/logos/2012/WomensDay-2012-hp.jpg"
End With
Catch ex As Exception
Dts.Events.FireError(0, "Problem connecting to website: ", ex.Message, "", 0)
End Try
Try
With webConnection
.DownloadFile(.BaseAddress, "D:\WomensDay-2012-hp.jpg")
End With
Catch ex As Exception
Dts.Events.FireError(0, "Problem downloading file: ", ex.Message, "", 0)
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
If anyone can point me in the right direction that would be magic.
Kind regards,
Chris
March 8, 2012 at 12:08 am
Well, at the end, this question has not much to do with SSIS or SQL, so maybe you have more luck in a .NET forum?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2012 at 1:06 pm
Hi there,
Indeed, I'd figured that may be the way of it. In any case, thank you for your comment, I'll give the .NET forums a try.
Kind regards,
Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply