Discovered this SSIS dataflow task called HTTPDownload. The link here is a dtsx package you can import into Visual Studio.
Modify the HTTP Connection Manager with the URL of the file. Note that this has to be specific, so the source file has to be the same if we are scheduling this as job to run on a regular basis.
Double-click on the “Download File” Control Flow and click Edit Script.
There are two lines to modify: This is the destination on the local server to copy the files. Change these two lines accordingly and save.
Dim filename As String = “C:TempFilename.xlsx”
connection.DownloadFile(“C:TempFilename”, True)
