ssis - capture name of downloaded file

• pvanhouse

  Old Hand

  Points: 312

  More actions

  April 4, 2016 at 9:47 am

  #317669

  I have an ssis package that downloads a file daily from a URL that does NOT contain the file name....such as https://xxx.yyy.uuu/consolidated_screening_list/search.tsv?api_key=OHZYuksFHSFao8jDXTkfiypO

  This works fine and the package saves the file to my SQL server's drive as "newfile.txt" and I can then process it using TSQL.

  However the vendor has changed the file layout to exclude the date the file was prepared (it's not always updated daily), and include that in the file name. So my script, which didn't used to care what the filename is...now at least needs to read the file name so I can get the last date the file was changed.

  When I run the URL manually from a browser I get the normal dialog: "Do you want to open or save File_2016_04_04.txt" with the choice of Open, Save, and Cancel.

  Is there any way to get my script to see this file name so I can extract the embedded date information. (2016-04-04.)

  My SSIS HTTP connection manager contains only the URL to the site: https://xxx.yyy.uuu/consolidated_screening_list/search.tsv?api_key=OHZYuksFHSFao8jDXTkfiypO

  Here is the ssis script Task that runs the download:

  Imports System

  Imports System.IO

  Imports System.Text

  Imports System.Windows.Forms

  Imports Microsoft.SqlServer.Dts.Runtime

  Public Class ScriptMain

  Public Sub Main()

  ' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"

  Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)

  ' Create a new HTTP client connection

  Dim connection As New HttpClientConnection(nativeObject)

  ' Save the file

  Dim filename As String = "C:\Tempewfile.txt"

  connection.DownloadFile(filename, True)

  Dts.TaskResult = Dts.Results.Success

  End Sub

  End Class