CODEWITHSUNDEEP
c#xmlssis
BCM
BCM

Reputation: 115

Odd Issue Downloading XML From URL

I am trying to load data from RSS feeds into a SQL Server database using SSIS and am running into issues connecting.

Example URL: https://ecf.akb.uscourts.gov/cgi-bin/rss_outside.pl (Can connect just fine from a web browser.)

I tried using this site as a template,https://www.mssqltips.com/sqlservertip/3141/importing-xml-documents-using-sql-server-integration-services/ , and everything went well, I was able to connect and even generate an .xsd file, but when I went to run it, I got a warning about the SSL\TSL certificate. I also tried using the built in Web Service task, but also ran into issues trying to download the WSLD file with the certificate.

Trying another avenue, based on these two sites, SSIS download from http - error SSL certification response obtained from server not valid and http://palkotools.blogspot.com/2011/06/tutorial-how-to-import-rss-feeds-into.html I instead tried using a C# Script task to download the XML data into a file, before attempting to process.

Using this example feed URL, the code worked just fine:

WebClient webClient = new WebClient();
webClient.DownloadFile(@"http://feeds.thehollywoodgossip.com/TheHollywoodGossip?format=xml", @"C:\RSS\RSSFile.xml");

However, when I try with the URL I need form the court, it fails:

WebClient webClient = new WebClient();
webClient.DownloadFile(@"https://ecf.akb.uscourts.gov/cgi-bin/rss_outside.pl?format=xml", @"C:\RSS\RSSFile.xml");

Is there something different\wrong with the court's URL? I am a total C# novice, so I hope this is something incredibly simple. Do any of you see what I am doing wrong or needs to be different from the example Hollywood gossip URL?

Thanks!

Edit: This is the error that gets returned with the court URL

   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Upvotes: 0

Views: 209

Answers (2)

Scrappy Coco
Scrappy Coco

Reputation: 564

Try to add this code at the first line:

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

Upvotes: 1

jdweng
jdweng

Reputation: 34421

Try following using xml linq. Not every entry has a href :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.Net;

namespace ConsoleApplication1
{
    class Program
    {
        const string URL = "http://feeds.thehollywoodgossip.com/TheHollywoodGossip?format=xml";
        static void Main(string[] args)
        {
            XDocument doc = XDocument.Load(URL);
            XElement feed = doc.Root;
            XNamespace ns = feed.GetDefaultNamespace();
            List<Entry> entries = feed.Elements(ns + "entry").Select(x => new Entry()
            {
                id = (string)x.Element(ns + "id"),
                published = (string)x.Element(ns + "published"),
                updated = (DateTime)x.Element(ns + "updated"),
                href = (string)x.Descendants(ns + "href").FirstOrDefault(),
                title = (string)x.Element(ns + "title"),
                content = (string)x.Element(ns + "content"),
                category = (string)x.Element(ns + "category"),
                author = (string)x.Element(ns + "author")
            }).ToList();

        }
    }
    public class Entry
    {
        public string id {get;set;}
        public string published {get;set;}
        public DateTime updated {get;set;}
        public string href {get;set;}
        public string title {get;set;}
        public string content {get;set;}
        public string category {get;set;}
        public string author { get; set; }
    }

}

Upvotes: 0

Related Questions