How to download from a dynamic link using SSIS?

Question

Hi everyone,

So I have a ssis package in which I download a excel file and import the contents of the file to SQL tables.

Issue I am facing is that the download link changes of the file frequently.

Today the download link is: https://www.uaeiec.gov.ae/API/Upload/DownloadFile?FileID=34cb35aa-3ba6-4e18-a950-f2b66f1cdf15

But tomorrow the "FileID=" will change as new file will be uploaded here:

https://www.uaeiec.gov.ae/en-us/un-page?p=1

Below is the code for downloading the file:

  		public void Main()
		{

            ServicePointManager.Expect100Continue = true;
            ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3 | SecurityProtocolType.Tls | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;


          try
            {


                //below code is for downloading UAE_LTL xls data
                string myURI5 = "https://www.uaeiec.gov.ae/API/Upload/DownloadFile?FileID=34a55617-5e44-49cd-985d-a56d2f2f85cd";
                string forSDN5 = "LS 230222.xls";
                string myWebString5 = null, myLocalPath5 = null;
                //forSDN4 = forSDN4.Replace(forSDN4, "sdn_xml.zip");
                myWebString5 = myURI5;// + forSDN5;
                myLocalPath5 = "D:\\6_downloaded_files\\" + forSDN5;
                WebClient myWebClient4 = new WebClient();
                myWebClient4.DownloadFile(myWebString5, myLocalPath5);

}


            catch (Exception ex)
            {
                Dts.Events.FireError(18, "The process failed", ex.ToString(), "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

	}
}

How can I get the latest download link? Can anyone help me?

Answer 1

@him tim, Welcome to Microsoft Q&A, you could try to get the specific fileid from the url.

First, please install nuget-package HtmlAgilityPack.

Then, you could try to use the following code to get the FileID:

 static void Main(string[] args)
        {
            string url = "https://www.uaeiec.gov.ae/en-us/un-page?p=1";
            HtmlWeb web = new HtmlWeb();
            HtmlDocument document = web.Load(url);
            var anchor = document.DocumentNode.SelectNodes("//h5");
  
            foreach (var item in anchor)
            {
                foreach (var att in item.GetAttributes())
                {
                    if (att.Name == "title"&&att.Value == "UNSC Resolution 1373")
                    {
                        string html = item.InnerHtml.Trim();
                        var doc = new HtmlDocument();
                        doc.LoadHtml(html);
                        string value = doc.DocumentNode.SelectSingleNode("//a").Attributes["href"].Value;
                        int index = value.IndexOf("FileID=");
                        string fileid=value.Substring(index+7);
                        Console.WriteLine( fileid);


                    }
                  

                }
          
            }
          

       }

Result:

The first result is I get from console and the second is I get from copy that link.

Hope my solution could be helpful for you.

Best Regards,

Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.