Download csv file from website which require authentication using SSIS

1
votes

I am trying to download a csv file from a Website which requires Login and Password using HTTP Connection Manager and Scrip Task but I just seem clueless and I have googled and I have had so many different approaches and I have tried and not succeeded.

I have managed to create HTTP Connection Manager with the link and Login and Password details and the Test Connection Succeeded. I have no clue how to write a script in Script Task which can download a csv file?

URL: https://feedback.X.X.X/practice/d8d44935/consult_admin_view and I need to click on the page to download the csv file but that doesn't have its own URL address.

The folder I need to download is C:\temp

Apologies if I sound too vague or clueless.

2
c#asp.netwebssisssis-2012
Does "click on the page to download" trigger a HTTP POST request? Does it cause some kind of JavaScript/AJAX call to occur? If we can figure out what clicking does behind-the-scenes, we might be able to automate it. – Ben Gribaudo
When I right click on the csv file and go to properties it has a Http address. – Djbril
Does pasting that address in a browser's address bar let you download the file? – Ben Gribaudo
So sorry for the late reply. Yes it does once I put in the username and password. – Djbril

2 Answers

0
votes

Here's some Visual Basic code I've used in an SSIS Script Task to achieve something similar. It assumes your HTTP connection is named HTTP_Test.

Dim mySSISConnection As Object = Dts.Connections("HTTP_Test").AcquireConnection(Nothing) Dim myConnection As HttpClientConnection = New HttpClientConnection(mySSISConnection) myConnection.ServerUserName = "myusername" myConnection.ServerUserName = "mypassword" myConnection.DownloadFile("C:\Temp\download.csv", True)

0
votes

I've done something similar using the PowerShell WebClient:

public void DownloadFile(
    string address,
    string fileName
)

This video should help you with the Automation of your ETL Infrastructure with SSIS and PowerShell.