Saturday, July 6, 2013

Consuming an ATOM 1.0 or RSS 2.0 Feed using SSIS (Part 2)

(<--Continued from Part 1)

Here is the code for main.cs in Script Component - Parse XML, (you will have to add System.ServiceModel as a reference):

#region Namespaces
using System;
using System.Data;
using System.IO;
using System.Net;
using System.ServiceModel.Syndication;
using System.Text;
using System.Xml;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections.Generic;
using System.Collections;

#endregion

#region Class
/// <summary>
/// This is the class to which to add your code.  Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    #region Methods
    /// <summary>
    /// This is the controlling method that handles the control flow for processing the feeds
    /// </summary>
    /// <param name="Row">This is the current row in the input buffer</param>
    private void ProcessFeed(Input0Buffer Row)
    {
        #region Variables
        //Converts our XML from the input buffer to string
        int xmlLen = Convert.ToInt32(Row.RAWXML.Length);
        byte[] xmlBlob = new byte[xmlLen];
        xmlBlob = Row.RAWXML.GetBlobData(0, xmlLen);
        //Set a text reader to our XML string
        TextReader tr = new StringReader(System.Text.Encoding.Unicode.GetString(xmlBlob));
        //Set our XmlReader to our TextReader
        XmlReader reader = XmlReader.Create(tr);
        //Load a SyndicationFeed object into a variable
        var feed = SyndicationFeed.Load(reader);
        //Holds image bytes
        byte[] imageBytes;
        //Holds the item id
        int itemId = 0;
        #endregion

        #region SyndicationFeed
        try
        {
            //Set image for feed if one exists
            if (feed.ImageUrl != null && feed.ImageUrl.AbsoluteUri != null)
            {
                //Gets image if present
                var webClient = new WebClient();
                imageBytes = webClient.DownloadData(feed.ImageUrl.AbsoluteUri);
            }
            else
            {
                imageBytes = null;
            }
        }
        catch (Exception e)
        {

            failComponent("Setting Image Column to Null -->" + e.ToString(), Row.CALLINGURI);
            imageBytes = null;
        }

        //Process top level syndication feed
        ProcessSyndicationFeed(SyndicationFeedOutBuffer, itemId, feed, xmlBlob, Row.HASHID, imageBytes, Row.FEEDTYPE, Row.CALLINGURI);

        //Process Each Extended Attribute
        {
            int attrId = 0;
            foreach (KeyValuePair<XmlQualifiedName, string> attr in feed.AttributeExtensions)
            {
                ProcessSyndicationFeedAttributes(attr, SyndicationFeedAttributesOutBuffer, Row.HASHID, itemId, attrId);
                attrId++;
            }
        }

        {
            int eleId = 0;
            //Process Each Extended Element
            foreach (SyndicationElementExtension ele in feed.ElementExtensions)
            {
                ProcessSyndicationFeedElements(ele, SyndicationFeedElementsOutBuffer, Row.HASHID, itemId, eleId);
                eleId++;
            }
        }
        {
            int personId = 0;
            //Process Authors
            for (int authorId = personId; authorId < feed.Authors.Count; authorId++)
            {

                ProcessSyndicationPersons(((SyndicationPerson)feed.Authors[authorId]), SyndicationPersonsOutBuffer, Row.HASHID, itemId, authorId, "Author");
                //Process Author Attributes
                {
                    int attrID = 0;
                    foreach (KeyValuePair<XmlQualifiedName, string> attr in ((SyndicationPerson)feed.Authors[authorId]).AttributeExtensions)
                    {
                        ProcessSyndicationPersonsAttributes(attr, SyndicationPersonsAttributesOutBuffer, Row.HASHID, authorId, attrID, itemId);
                        attrID++;
                    }
                }

                {
                    int eleId = 0;
                    //Process Author Elements
                    foreach (SyndicationElementExtension ele in ((SyndicationPerson)feed.Authors[authorId]).ElementExtensions)
                    {
                        ProcessSyndicationPersonsElements(ele, SyndicationPersonsElementsOutBuffer, Row.HASHID, authorId, itemId, eleId);
                        eleId++;
                    }
                }
                //So we can make sure that contributors continues the PERSON_ID iterator since they share a DB table
                personId = authorId + 1;
            }

            //Process Contributors
            for (int contributorId = personId; contributorId < feed.Contributors.Count + personId; contributorId++)
            {

                ProcessSyndicationPersons(((SyndicationPerson)feed.Contributors[contributorId]), SyndicationPersonsOutBuffer, Row.HASHID, itemId, contributorId, "Contributor");

                {
                    int attrId = 0;
                    //Process Contributor Attributes
                    foreach (KeyValuePair<XmlQualifiedName, string> attr in ((SyndicationPerson)feed.Contributors[contributorId]).AttributeExtensions)
                    {
                        ProcessSyndicationPersonsAttributes(attr, SyndicationPersonsAttributesOutBuffer, Row.HASHID, contributorId, attrId, itemId);
                        attrId++;
                    }
                }
                {
                    int eleId = 0;
                    //Process Contributor Elements
                    foreach (SyndicationElementExtension ele in ((SyndicationPerson)feed.Contributors[contributorId]).ElementExtensions)
                    {
                        ProcessSyndicationPersonsElements(ele, SyndicationPersonsElementsOutBuffer, Row.HASHID, contributorId, itemId, eleId);
                        eleId++;
                    }
                }

            }
        }

        //Process Categories
        for (int categoryId = 0; categoryId < feed.Categories.Count; categoryId++)
        {
            ProcessSyndicationCategories(((SyndicationCategory)feed.Categories[categoryId]), SyndicationCategoriesOutBuffer, Row.HASHID, itemId, categoryId);

            {
                int attrId = 0;
                //Process Category Attributes
                foreach (KeyValuePair<XmlQualifiedName, string> attr in ((SyndicationCategory)feed.Categories[categoryId]).AttributeExtensions)
                {
                    ProcessSyndicationCategoriesAttributes(attr, SyndicationCategoriesAttributesOutBuffer, Row.HASHID, categoryId, attrId, itemId);
                    attrId++;
                }
            }

            {
                int eleId = 0;
                //Process Category Elements
                foreach (SyndicationElementExtension ele in ((SyndicationCategory)feed.Categories[categoryId]).ElementExtensions)
                {
                    ProcessSyndicationCategoriesElements(ele, SyndicationCategoriesElementsOutBuffer, Row.HASHID, categoryId, itemId, eleId);
                    eleId++;
                }
            }


        }

        //Process Links
        for (int linkId = 0; linkId < feed.Links.Count; linkId++)
        {
            ProcessSyndicationLinks(((SyndicationLink)feed.Links[linkId]), SyndicationLinksOutBuffer, Row.HASHID, itemId, linkId);

            {
                int attrId = 0;
                //Process Link Attributes
                foreach (KeyValuePair<XmlQualifiedName, string> attr in ((SyndicationLink)feed.Links[linkId]).AttributeExtensions)
                {
                    ProcessSyndicationLinksAttributes(attr, SyndicationLinksAttributesOutBuffer, Row.HASHID, linkId, attrId, itemId);
                    attrId++;
                }
            }

            {
                int eleId = 0;
                //Process Link Elements
                foreach (SyndicationElementExtension ele in ((SyndicationLink)feed.Links[linkId]).ElementExtensions)
                {
                    ProcessSyndicationLinksElements(ele, SyndicationLinksElementsOutBuffer, Row.HASHID, linkId, itemId, eleId);
                    eleId++;
                }
            }

        }
        #endregion

        #region SyndicationItem

        //set itemId to 1
        itemId++;
        //Process Syndication Items
        foreach (SyndicationItem item in feed.Items)
        {


            //Process top level syndication item
            ProcessSyndicationFeed(SyndicationFeedOutBuffer, itemId, item, Row.HASHID, Row.FEEDTYPE, Row.CALLINGURI, feed.Language);

            {
                int attrId = 0;
                //Process Each Extended Attribute
                foreach (KeyValuePair<XmlQualifiedName, string> attr in item.AttributeExtensions)
                {
                    ProcessSyndicationFeedAttributes(attr, SyndicationFeedAttributesOutBuffer, Row.HASHID, itemId, attrId);
                    attrId++;
                }
            }
            {
                int eleId = 0;
                //Process Each Extended Element
                foreach (SyndicationElementExtension ele in item.ElementExtensions)
                {
                    ProcessSyndicationFeedElements(ele, SyndicationFeedElementsOutBuffer, Row.HASHID, itemId, eleId);
                    eleId++;
                }
            }

            {
                int personId = 0;
                //Process Authors
                for (int authorId = personId; authorId < item.Authors.Count; authorId++)
                {

                    ProcessSyndicationPersons(((SyndicationPerson)item.Authors[authorId]), SyndicationPersonsOutBuffer, Row.HASHID, itemId, authorId, "Author");
                    //Process Author Attributes
                    {
                        int attrId = 0;
                        foreach (KeyValuePair<XmlQualifiedName, string> attr in ((SyndicationPerson)item.Authors[authorId]).AttributeExtensions)
                        {
                            ProcessSyndicationPersonsAttributes(attr, SyndicationPersonsAttributesOutBuffer, Row.HASHID, authorId, attrId, itemId);
                            attrId++;
                        }
                    }
                    {
                        int eleId = 0;
                        //Process Author Elements
                        foreach (SyndicationElementExtension ele in ((SyndicationPerson)item.Authors[authorId]).ElementExtensions)
                        {
                            ProcessSyndicationPersonsElements(ele, SyndicationPersonsElementsOutBuffer, Row.HASHID, authorId, itemId, eleId);
                            eleId++;
                        }
                    }
                    //So we can make sure that contributors continues the PERSON_ID iterator since they share a DB table
                    personId = authorId + 1;
                }


                //Process Contributors
                for (int contributorId = personId; contributorId < item.Contributors.Count + personId; contributorId++)
                {

                    ProcessSyndicationPersons(((SyndicationPerson)item.Contributors[contributorId]), SyndicationPersonsOutBuffer, Row.HASHID, itemId, contributorId, "Contributor");
                    {
                        int attrId = 0;
                        //Process Contributor Attributes
                        foreach (KeyValuePair<XmlQualifiedName, string> attr in ((SyndicationPerson)item.Contributors[contributorId]).AttributeExtensions)
                        {
                            ProcessSyndicationPersonsAttributes(attr, SyndicationPersonsAttributesOutBuffer, Row.HASHID, contributorId, attrId, itemId);
                            attrId++;
                        }
                    }
                    {
                        int eleId = 0;
                        //Process Contributor Elements
                        foreach (SyndicationElementExtension ele in ((SyndicationPerson)item.Contributors[contributorId]).ElementExtensions)
                        {
                            ProcessSyndicationPersonsElements(ele, SyndicationPersonsElementsOutBuffer, Row.HASHID, contributorId, itemId, eleId);
                            eleId++;
                        }


                    }
                }
            }


            //Process Categories
            for (int categoryId = 0; categoryId < item.Categories.Count; categoryId++)
            {
                ProcessSyndicationCategories(((SyndicationCategory)item.Categories[categoryId]), SyndicationCategoriesOutBuffer, Row.HASHID, itemId, categoryId);

                {
                    int attrId = 0;
                    //Process Category Attributes
                    foreach (KeyValuePair<XmlQualifiedName, string> attr in ((SyndicationCategory)item.Categories[categoryId]).AttributeExtensions)
                    {
                        ProcessSyndicationCategoriesAttributes(attr, SyndicationCategoriesAttributesOutBuffer, Row.HASHID, categoryId, attrId, itemId);
                        attrId++;
                    }
                }

                {
                    int eleId = 0;
                    //Process Category Elements
                    foreach (SyndicationElementExtension ele in ((SyndicationCategory)item.Categories[categoryId]).ElementExtensions)
                    {
                        ProcessSyndicationCategoriesElements(ele, SyndicationCategoriesElementsOutBuffer, Row.HASHID, categoryId, itemId, eleId);
                        eleId++;
                    }
                }


            }
            //Process Links
            for (int linkId = 0; linkId < item.Links.Count; linkId++)
            {
                ProcessSyndicationLinks(((SyndicationLink)item.Links[linkId]), SyndicationLinksOutBuffer, Row.HASHID, itemId, linkId);

                {
                    int attrId = 0;
                    //Process Link Attributes
                    foreach (KeyValuePair<XmlQualifiedName, string> attr in ((SyndicationLink)item.Links[linkId]).AttributeExtensions)
                    {
                        ProcessSyndicationLinksAttributes(attr, SyndicationLinksAttributesOutBuffer, Row.HASHID, linkId, attrId, itemId);
                        attrId++;
                    }

                }
                {
                    int eleId = 0;
                    //Process Link Elements
                    foreach (SyndicationElementExtension ele in ((SyndicationLink)item.Links[linkId]).ElementExtensions)
                    {
                        ProcessSyndicationLinksElements(ele, SyndicationLinksElementsOutBuffer, Row.HASHID, linkId, itemId, eleId);
                        eleId++;
                    }
                }
            }


            if (item.Content != null)
            {
                {
                    int attrId = 0;
                    //Process Content Attributes
                    foreach (KeyValuePair<XmlQualifiedName, string> attr in item.Content.AttributeExtensions)
                    {
                        ProcessSyndicationContentAttributes(attr, SyndicationContentAttributesOutBuffer, Row.HASHID, attrId, itemId);
                        attrId++;
                    }
                }
            }

            if (item.Summary != null)
            {
                {
                    int attrId = 0;
                    //Process Summary Attributes
                    foreach (KeyValuePair<XmlQualifiedName, string> attr in item.Summary.AttributeExtensions)
                    {
                        ProcessSyndicationSummaryAttributes(attr, SyndicationSummaryAttributesOutBuffer, Row.HASHID, attrId, itemId);
                        attrId++;
                    }

                }


            }

            itemId++;
        #endregion

        }
    }
    /// <summary>
    /// This method parses out the attribute values associated with feed content and outputs to Syndication_Content_Attributes_Out
    /// </summary>
    /// <param name="attr">This is A key value pair that contains the attribute label name and value</param>
    /// <param name="outFeed">This is the attribute content output buffer</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="attributeId">This is a unique identifier for the attribute within the context of the content</param>
    /// <param name="itemId">This is the unique identifier for the parent feed record of the content</param>
    private void ProcessSyndicationContentAttributes(KeyValuePair<XmlQualifiedName, string> attr, SyndicationContentAttributesOutBuffer outFeed, string hashId, int attributeId, int itemId)
    {
        outFeed.AddRow();
        outFeed.HASHID = hashId;
        outFeed.ATTRIBUTEID = attributeId;
        outFeed.ITEMID = itemId;
        if (attr.Key.Name != null)
        {
            outFeed.NAME = attr.Key.Name;
        }
        else
        {
            outFeed.NAME_IsNull = true;
        }
        if (attr.Value != null)
        {
            outFeed.VALUE.AddBlobData(Encoding.Unicode.GetBytes(attr.Value));
        }
        else
        {
            outFeed.VALUE.SetNull();
        }
    }
    /// <summary>
    /// This method parses out the attribute values associated with feed summaries and outputs to Syndication_Summary_Attributes_Out
    /// </summary>
    /// <param name="attr">This is A key value pair that contains the attribute label name and value</param>
    /// <param name="outFeed">This is the attribute summary output buffer</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="attributeId">This is a unique identifier for the attribute within the context of the summary</param>
    /// <param name="itemId">This is the unique identifier for the parent feed record of the summary</param>
    private void ProcessSyndicationSummaryAttributes(KeyValuePair<XmlQualifiedName, string> attr, SyndicationSummaryAttributesOutBuffer outFeed, string hashId, int attributeId, int itemId)
    {
        outFeed.AddRow();
        outFeed.HASHID = hashId;
        outFeed.ATTRIBUTEID = attributeId;
        outFeed.ITEMID = itemId;
        if (attr.Key.Name != null)
        {
            outFeed.NAME = attr.Key.Name;
        }
        else
        {
            outFeed.NAME_IsNull = true;
        }
        if (attr.Value != null)
        {
            outFeed.VALUE.AddBlobData(Encoding.Unicode.GetBytes(attr.Value));
        }
        else
        {
            outFeed.VALUE.SetNull();
        }
    }

    /// <summary>
    /// This method is called once for every row that passes through the component from Input0.
    ///
    /// Example of reading a value from a column in the the row:
    ///  string zipCode = Row.ZipCode
    ///
    /// Example of writing a value to a column in the row:
    ///  Row.ZipCode = zipCode
    /// </summary>
    /// <param name="Row">The row that is currently passing through the component</param>
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        //Parse out the XML in the feed
          try
          {
              ProcessFeed(Row);
          }
          catch (Exception e)
          {
              failComponent(e.ToString(), Row.CALLINGURI);

          }
    }

    /// <summary>
    /// This method parses out the main feed item elements and outputs to Syndication_Feed_Out
    /// </summary>
    /// <param name="outFeed">This is the syndication feed output buffer</param>
    /// <param name="itemId">This is the unique identifier for the parent feed</param>
    /// <param name="item">This is the SyndicationItem object</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="feedType">This is a string passed in from the Row that tells if the feed is Unknown, ATOM 1.0 or RSS 2.0</param>
    /// <param name="callingUri">This is the URI called to get the feed</param>
    /// <param name="language">This is the language of the feed</param>
    private void ProcessSyndicationFeed(SyndicationFeedOutBuffer outFeed, int itemId, SyndicationItem item, string hashId, string feedType, string callingUri, string language)
    {

        outFeed.AddRow();
        outFeed.ITEMID = itemId;
        if (item.Id != null)
        {
            outFeed.ID = item.Id;
        }
        else
        {
            outFeed.ID_IsNull = true;
        }
        if (item.Copyright != null && item.Copyright.Text != null)
        {
            outFeed.COPYRIGHT = item.Copyright.Text;
        }
        else
        {
            outFeed.COPYRIGHT_IsNull = true;
        }
        if (item.Content != null && ((TextSyndicationContent)item.Content).Text != null)
        {
            outFeed.DESCRIPTION.AddBlobData(Encoding.Unicode.GetBytes(((TextSyndicationContent)item.Content).Text));
        }
        else
        {
            outFeed.DESCRIPTION.SetNull();
        }
        if (item.BaseUri != null && item.BaseUri.AbsoluteUri != null)
        {
            outFeed.BASEURI = item.BaseUri.AbsoluteUri;
        }
        {
            outFeed.BASEURI_IsNull = true;
        }

        outFeed.IMAGEURL_IsNull = true;
        outFeed.IMAGESTORED.SetNull();
        outFeed.GENERATOR_IsNull = true;
        if (language != null)
        {
            outFeed.LANGUAGE = language;
        }
        else
        {
            outFeed.LANGUAGE_IsNull = true;
        }
        if (item.LastUpdatedTime != null)
        {
            outFeed.LASTUPDATEDTIME = item.LastUpdatedTime.ToString();
        }
        else
        {
            outFeed.LASTUPDATEDTIME_IsNull = true;
        }
        if (item.Title.Text != null)
        {
            outFeed.TITLE = item.Title.Text;
        }
        else
        {
            outFeed.TITLE_IsNull = true;
        }
        outFeed.FEEDTYPE = feedType;
        outFeed.HASHID = hashId;
        outFeed.RAWXML.SetNull();
        if (callingUri != null)
        {
            outFeed.CALLINGURI = callingUri;
        }
        else
        {
            outFeed.CALLINGURI_IsNull = true;
        }
        if (item.PublishDate != null)
        {
            outFeed.PUBDATE = item.PublishDate.ToString();
        }
        else
        {
            outFeed.PUBDATE_IsNull = true;
        }
        if (item.Summary != null && item.Summary.Text != null)
        {
            outFeed.SUMMARY.AddBlobData(Encoding.Unicode.GetBytes(item.Summary.Text));
        }
        else
        {
            outFeed.SUMMARY.SetNull();
        }
        if (item.Summary != null && item.Summary.Type != null)
        {
            outFeed.SUMMARYTYPE = item.Summary.Type;
        }
        else
        {
            outFeed.SUMMARYTYPE_IsNull = true;
        }
        if (item.Content != null && item.Content.Type != null)
        {
            outFeed.CONTENTTYPE = item.Content.Type;
        }
        else
        {
            outFeed.CONTENTTYPE_IsNull = true;
        }
    }
    /// <summary>
    /// This method parses out the link elements and outputs to Syndication_Feed_Elements_Out
    /// </summary>
    /// <param name="ele">This is the SyndicationElementExtension object</param>
    /// <param name="outFeed">This is the syndication link element output buffer</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="linkId">This is a unique identifier for the link within the context of the parent feed</param>
    /// <param name="itemId">This is the unique identifier for the parent feed</param>
    /// <param name="elementId">This is a unique identifier for the element within the context of the link</param>
    ///
    private void ProcessSyndicationLinksElements(SyndicationElementExtension ele, SyndicationLinksElementsOutBuffer outFeed, string hashId, int linkId, int itemId, int elementId)
    {
        outFeed.AddRow();
        outFeed.HASHID = hashId;
        outFeed.LINKID = linkId;
        outFeed.ELEMENTID = elementId;
        outFeed.ITEMID = itemId;
        if (ele.OuterName != null)
        {
            outFeed.OUTERNAME = ele.OuterName;
        }
        else
        {
            outFeed.OUTERNAME_IsNull = true;
        }
        if (ele.OuterNamespace != null)
        {
            outFeed.OUTERNAMESPACE = ele.OuterNamespace;
        }
        else
        {
            outFeed.OUTERNAMESPACE_IsNull = true;
        }
        if (ele.GetObject<XmlElement>() != null)
        {
            outFeed.VALUE.AddBlobData(Encoding.Unicode.GetBytes(ele.GetObject<string>()));
        }
        else
        {
            outFeed.VALUE.SetNull();
        }

    }
    /// <summary>
    /// This method parses out the link attributes and outputs to Syndication_Links_Attributes_Out
    /// </summary>
    /// <param name="attr">This is the KeyValuePair object that contains the link attribute properties</param>
    /// <param name="outFeed">This is the syndication link attribute output buffer</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="linkId">This is a unique identifier for the link within the context of the parent feed</param>
    /// <param name="attributeId">This is a unique identifier for the attribute within the context of the link</param>
    /// <param name="itemId">This is the unique identifier for the parent feed</param>
    private void ProcessSyndicationLinksAttributes(KeyValuePair<XmlQualifiedName, string> attr, SyndicationLinksAttributesOutBuffer outFeed, string hashId, int linkId, int attributeId, int itemId)
    {
        outFeed.AddRow();
        outFeed.HASHID = hashId;
        outFeed.LINKID = linkId;
        outFeed.ATTRIBUTEID = attributeId;
        outFeed.ITEMID = itemId;
        if (attr.Key.Name != null)
        {
            outFeed.NAME = attr.Key.Name;
        }
        else
        {
            outFeed.NAME_IsNull = true;
        }
        if (attr.Value != null)
        {
            outFeed.VALUE.AddBlobData(Encoding.Unicode.GetBytes(attr.Value));
        }
        else
        {
            outFeed.VALUE.SetNull();
        }
    }
    /// <summary>
    /// This method parses out the link and outputs to Syndication_Links_Out
    /// </summary>
    /// <param name="link">This is the SyndicationLink object</param>
    /// <param name="outFeed">This is the syndication link output buffer</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="itemId">This is the unique identifier for the parent feed</param>
    /// <param name="linkId">This is a unique identifier for the link within the context of the parent feed</param>
    private void ProcessSyndicationLinks(SyndicationLink link, SyndicationLinksOutBuffer outFeed, string hashId, int itemId, int linkId)
    {
        outFeed.AddRow();
        outFeed.HASHID = hashId;
        outFeed.LINKID = linkId;
        outFeed.ITEMID = itemId;
        if (link.BaseUri != null && link.BaseUri.AbsoluteUri != null)
        {
            outFeed.BASEURI = link.BaseUri.AbsoluteUri;
        }
        else
        {
            outFeed.BASEURI_IsNull = true;
        }
        if (link.Length != null)
        {
            outFeed.LENGTH = link.Length;
        }
        else
        {
            outFeed.LENGTH_IsNull = true;
        }
        if (link.MediaType != null)
        {
            outFeed.MEDIATYPE = link.MediaType;
        }
        else
        {
            outFeed.MEDIATYPE_IsNull = true;
        }
        if (link.RelationshipType != null)
        {
            outFeed.RELATIONSHIPTYPE = link.RelationshipType;
        }
        else
        {
            outFeed.RELATIONSHIPTYPE_IsNull = true;
        }
        if (link.Title != null)
        {
            outFeed.TITLE = link.Title;
        }
        else
        {
            outFeed.TITLE_IsNull = true;
        }
        if (link.Uri != null && link.Uri.AbsoluteUri != null)
        {
            outFeed.URI = link.Uri.AbsoluteUri;
        }
        else
        {
            outFeed.URI_IsNull = true;
        }


    }

    /// <summary>
    /// This method parses out the category elements and outputs to Syndication_Categories_Elements_Out
    /// </summary>
    /// <param name="ele">This is the SyndicationElementExtentsion object</param>
    /// <param name="outFeed">This is the syndication category element output buffer</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="categoryId">This is a unique identifier for the category within the context of the parent feed</param>
    /// <param name="itemId">This is the unique identifier for the parent feed</param>
    /// <param name="elementId">This is a unique identifier for the element within the context of the category</param>
    private void ProcessSyndicationCategoriesElements(SyndicationElementExtension ele, SyndicationCategoriesElementsOutBuffer outFeed, string hashId, int categoryId, int itemId, int elementId)
    {
        outFeed.AddRow();
        outFeed.HASHID = hashId;
        outFeed.CATEGORYID = categoryId;
        outFeed.ELEMENTID = elementId;
        outFeed.ITEMID = itemId;
        if (ele.OuterName != null)
        {
            outFeed.OUTERNAME = ele.OuterName;
        }
        else
        {
            outFeed.OUTERNAME_IsNull = true;
        }
        if (ele.OuterNamespace != null)
        {
            outFeed.OUTERNAMESPACE = ele.OuterNamespace;
        }
        else
        {
            outFeed.OUTERNAMESPACE_IsNull = true;
        }
        if (ele.GetObject<XmlElement>() != null)
        {
            outFeed.VALUE.AddBlobData(Encoding.Unicode.GetBytes(ele.GetObject<string>()));
        }
        else
        {
            outFeed.VALUE.SetNull();
        }
    }
    /// <summary>
    /// This method parses out the category attributes and outputs to Syndication_Categories_Attributes_Out
    /// </summary>
    /// <param name="attr">This is the KeyValuePair object that contains the category attribute properties</param>
    /// <param name="outFeed">This is the syndication category attribute output buffer</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="categoryId">This is a unique identifier for the category within the context of the parent feed</param>
    /// <param name="attributeId">This is a unique identifier for the attribute within the context of the category</param>
    /// <param name="itemId">This is the unique identifier for the parent feed</param>
    private void ProcessSyndicationCategoriesAttributes(KeyValuePair<XmlQualifiedName, string> attr, SyndicationCategoriesAttributesOutBuffer outFeed, string hashId, int categoryId, int attributeId, int itemId)
    {
        outFeed.AddRow();
        outFeed.HASHID = hashId;
        outFeed.CATEGORYID = categoryId;
        outFeed.ATTRIBUTEID = attributeId;
        outFeed.ITEMID = itemId;
        if (attr.Key.Name != null)
        {
            outFeed.NAME = attr.Key.Name;
        }
        else
        {
            outFeed.ATTRIBUTEID_IsNull = true;
        }
        if (attr.Value != null)
        {
            outFeed.VALUE.AddBlobData(Encoding.Unicode.GetBytes(attr.Value));
        }
        else
        {
            outFeed.VALUE.SetNull();
        }
    }
    /// <summary>
    /// This method parses out the category and outputs to Syndication_Categories_Out
    /// </summary>
    /// <param name="cat">This is the SyndicationCategory object</param>
    /// <param name="outFeed">This is the syndication category output buffer</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="itemId">This is the unique identifier for the parent feed</param>
    /// <param name="categoryId">This is a unique identifier for the category within the context of the parent feed</param>
    private void ProcessSyndicationCategories(SyndicationCategory cat, SyndicationCategoriesOutBuffer outFeed, string hashId, int itemId, int categoryId)
    {
        outFeed.AddRow();
        outFeed.HASHID = hashId;
        outFeed.CATEGORYID = categoryId;
        outFeed.ITEMID = itemId;
        if (cat.Label != null)
        {
            outFeed.LABEL = cat.Label;
        }
        else
        {
            outFeed.LABEL_IsNull = true;
        }
        if (cat.Name != null)
        {
            outFeed.NAME = cat.Name;
        }
        else
        {
            outFeed.NAME_IsNull = true;
        }
        if (cat.Scheme != null)
        {
            outFeed.SCHEME = cat.Scheme;
        }
        else
        {
            outFeed.SCHEME_IsNull = true;
        }
    }
    /// <summary>
    /// This method parses out the person elements and outputs to Syndication_Persons_Elements_Out
    /// </summary>
    /// <param name="ele">This is the SyndicationElementExtension object</param>
    /// <param name="outFeed">This is the syndication person element output buffer</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="personId">This is a unique identifier for the person within the context of the parent feed</param>
    /// <param name="itemId">This is the unique identifier for the parent feed</param>
    /// <param name="elementId">This is a unique identifier for the element within the context of the person</param>
    private void ProcessSyndicationPersonsElements(SyndicationElementExtension ele, SyndicationPersonsElementsOutBuffer outFeed, string hashId, int personId, int itemId, int elementId)
    {
        outFeed.AddRow();
        outFeed.HASHID = hashId;
        outFeed.PERSONID = personId;
        outFeed.ELEMENTID = elementId;
        outFeed.ITEMID = itemId;
        if (ele.OuterName != null)
        {
            outFeed.OUTERNAME = ele.OuterName;
        }
        else
        {
            outFeed.OUTERNAME_IsNull = true;
        }
        if (ele.OuterNamespace != null)
        {
            outFeed.OUTERNAMESPACE = ele.OuterNamespace;
        }
        else
        {
            outFeed.OUTERNAMESPACE_IsNull = true;
        }
        if (ele.GetObject<XmlElement>() != null)
        {
            outFeed.VALUE.AddBlobData(Encoding.Unicode.GetBytes(ele.GetObject<string>()));
        }
        else
        {
            outFeed.VALUE.SetNull();
        }

    }
    /// <summary>
    /// This method parses out the person attributes and outputs to Syndication_Persons_Attributes_Out
    /// </summary>
    /// <param name="attr">This is the KeyValuePair object that contains the person attribute properties</param>
    /// <param name="outFeed">This is the syndication person attribute output buffer</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="personId">This is a unique identifier for the person within the context of the parent feed</param>
    /// <param name="attributeId">This is a unique identifier for the attribute within the context of the person</param>
    /// <param name="itemId">This is the unique identifier for the parent feed</param>
    private void ProcessSyndicationPersonsAttributes(KeyValuePair<XmlQualifiedName, string> attr, SyndicationPersonsAttributesOutBuffer outFeed, string hashId, int personId, int attributeId, int itemId)
    {
        outFeed.AddRow();
        outFeed.HASHID = hashId;
        outFeed.PERSONID = personId;
        outFeed.ATTRIBUTEID = attributeId;
        outFeed.ITEMID = itemId;
        if (attr.Key.Name != null)
        {
            outFeed.NAME = attr.Key.Name;
        }
        else
        {
            outFeed.ATTRIBUTEID_IsNull = true;
        }
        if (attr.Value != null)
        {
            outFeed.VALUE.AddBlobData(Encoding.Unicode.GetBytes(attr.Value));
        }
        else
        {
            outFeed.VALUE.SetNull();
        }
    }
    /// <summary>
    /// This method parses out the persons and outputs to Syndication_Persons_Out
    /// </summary>
    /// <param name="person">This is the SyndicationPerson object</param>
    /// <param name="outFeed">This is the syndication person output buffer</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="personId">This is a unique identifier for the person within the context of the parent feed</param>
    /// <param name="itemId">This is the unique identifier for the parent feed</param>
    /// <param name="type">This determines whether or not the person is an Author or a Contributor</param>
    private void ProcessSyndicationPersons(SyndicationPerson person, SyndicationPersonsOutBuffer outFeed, string hashId, int personId, int itemId, string type)
    {
        outFeed.AddRow();
        outFeed.HASHID = hashId;
        outFeed.PERSONID = personId;
        outFeed.ITEMID = itemId;
        if (person.Name != null)
        {
            outFeed.NAME = person.Name;
        }
        else
        {
            outFeed.NAME_IsNull = true;
        }
        if (person.Email != null)
        {
            outFeed.EMAIL = person.Email;
        }
        if (person.Uri != null)
        {
            outFeed.URI = person.Uri;
        }
        else
        {
            outFeed.URI_IsNull = true;
        }
        outFeed.TYPE = type;
    }
    /// <summary>
    /// This method parses out the syndication feed elements and outputs to Syndication_Feed_Elements_Out
    /// </summary>
    /// <param name="ele">This is the SyndicationElementExtension object</param>
    /// <param name="outFeed">This is the syndication feed element output buffer</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="itemID">This is the unique identifier for the parent feed</param>
    /// <param name="elementId">This is a unique identifier for the element within the context of the parent feed</param>
    private void ProcessSyndicationFeedElements(SyndicationElementExtension ele, SyndicationFeedElementsOutBuffer outFeed, string hashId, int itemID, int elementId)
    {

        outFeed.AddRow();
        outFeed.HASHID = hashId;
        outFeed.ELEMENTID = elementId;
        outFeed.ITEMID = itemID;
        if (ele.OuterName != null)
        {
            outFeed.OUTERNAME = ele.OuterName;
        }
        else
        {
            outFeed.OUTERNAME_IsNull = true;
        }
        if (ele.OuterNamespace != null)
        {
            outFeed.OUTERNAMESPACE = ele.OuterNamespace;
        }
        else
        {
            outFeed.OUTERNAMESPACE_IsNull = true;
        }
        if (ele.GetObject<XmlElement>() != null)
        {

            outFeed.VALUE.AddBlobData(Encoding.Unicode.GetBytes(ele.GetObject<string>()));

        }
        else
        {
            outFeed.VALUE.SetNull();
        }

    }
    /// <summary>
    /// This method parses out the syndication feed and outputs to Syndication_Feed_Out
    /// </summary>
    /// <param name="outFeed">This is the syndication feed output buffer</param>
    /// <param name="itemid">This is the unique identifier for the feed</param>
    /// <param name="feed">This is the SyndicationFeed object</param>
    /// <param name="xmlBlob">This is the byte array representation of the feed's XML</param>
    /// <param name="hashID">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="imageBytes">This is the byte array representation of the feed's image</param>
    /// <param name="feedType">This determines whether the feed is Unknown, ATOM 1.0 or RSS 2.0</param>
    /// <param name="callingUri">This is the URI called to get the feed</param>
    private void ProcessSyndicationFeed(SyndicationFeedOutBuffer outFeed, int itemid, SyndicationFeed feed, byte[] xmlBlob, string hashID, byte[] imageBytes, string feedType, string callingUri)
    {

        outFeed.AddRow();
        outFeed.ITEMID = itemid;
        if (feed.Id != null)
        {
            outFeed.ID = feed.Id;
        }
        else
        {
            outFeed.ID_IsNull = true;
        }
        if (feed.Copyright != null && feed.Copyright.Text != null)
        {
            outFeed.COPYRIGHT = feed.Copyright.Text;
        }
        else
        {
            outFeed.COPYRIGHT_IsNull = true;
        }
        if (feed.Description != null && feed.Description.Text != null)
        {
            outFeed.DESCRIPTION.AddBlobData(Encoding.Unicode.GetBytes(feed.Description.Text));
        }
        else
        {
            outFeed.DESCRIPTION.SetNull();
        }
        if (feed.BaseUri != null && feed.BaseUri.AbsoluteUri != null)
        {
            outFeed.BASEURI = feed.BaseUri.AbsoluteUri;
        }
        {
            outFeed.BASEURI_IsNull = true;
        }
        if (feed.ImageUrl != null && feed.ImageUrl.AbsoluteUri != null)
        {
            outFeed.IMAGEURL = feed.ImageUrl.AbsoluteUri;
        }
        else
        {
            outFeed.IMAGEURL_IsNull = true;
        }
        if (imageBytes != null)
        {
            outFeed.IMAGESTORED.AddBlobData(imageBytes);
        }
        else
        {
            outFeed.IMAGESTORED.SetNull();
        }
        if (feed.Generator != null)
        {
            outFeed.GENERATOR = feed.Generator;
        }
        else
        {
            outFeed.GENERATOR_IsNull = true;
        }
        if (feed.Language != null)
        {
            outFeed.LANGUAGE = feed.Language;
        }
        else
        {
            outFeed.LANGUAGE_IsNull = true;
        }
        if (feed.LastUpdatedTime != null)
        {
            outFeed.LASTUPDATEDTIME = feed.LastUpdatedTime.ToString();
        }
        else
        {
            outFeed.LASTUPDATEDTIME_IsNull = true;
        }
        if (feed.Title.Text != null)
        {
            outFeed.TITLE = feed.Title.Text;
        }
        else
        {
            outFeed.TITLE_IsNull = true;
        }
        outFeed.FEEDTYPE = feedType;
        outFeed.HASHID = hashID;
        outFeed.RAWXML.AddBlobData(xmlBlob);
        if (callingUri != null)
        {
            outFeed.CALLINGURI = callingUri;
        }
        else
        {
            outFeed.CALLINGURI_IsNull = true;
        }
        outFeed.PUBDATE_IsNull = true;
        outFeed.SUMMARY.SetNull();
        outFeed.SUMMARYTYPE_IsNull = true;
        outFeed.CONTENTTYPE_IsNull = true;

    }
    /// <summary>
    /// This method parses out the syndication feed attributes and outputs to Syndication_Feed_Attributes_Out
    /// </summary>
    /// <param name="attr">This is the KeyValuePair object that contains the syndication feed attribute properties</param>
    /// <param name="outFeed">This is the syndication feed output buffer</param>
    /// <param name="hashId">This is the unqiue SHA1 hash for the feed</param>
    /// <param name="itemId">This is the unique identifier for the feed</param>
    /// <param name="attributeId">This is a unique identifier for the attribute within the context of the parent feed</param>
    private void ProcessSyndicationFeedAttributes(KeyValuePair<XmlQualifiedName, string> attr, SyndicationFeedAttributesOutBuffer outFeed, string hashId, int itemId, int attributeId)
    {
        outFeed.AddRow();
        outFeed.HASHID = hashId;
        outFeed.ATTRIBUTEID = attributeId;
        outFeed.ITEMID = itemId;
        if (attr.Key.Name != null)
        {
            outFeed.NAME = attr.Key.Name;
        }
        else
        {
            outFeed.NAME_IsNull = true;
        }
        if (attr.Value != null)
        {
            outFeed.VALUE.AddBlobData(Encoding.Unicode.GetBytes(attr.Value));
        }
        else
        {
            outFeed.VALUE.SetNull();
        }

    }

    /// <summary>
    /// Outputs an error message
    /// </summary>
    /// <param name="errorMsg">The exception message</param>
    /// /// <param name="feedLink">The link to the feed being processed</param>
    private void failComponent(string errorMsg, string feedLink)
    {
        bool fail = false;
        IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;
        compMetadata.FireError(1, "Error Parsing Feed ("+feedLink+")!", errorMsg, "", 0, out fail);
    }

    #endregion



}

#endregion


Build, save and run the package. All 6 feeds should now be parsed out into your database. 

4 comments:

  1. Thanks for all the work on this. It was long. I'm having an issue with the last script component. I'm getting a bunch of errors in the objects like rawxml, callinguri, hashid and so on. I added the ServiceModel reference to the last script task code so I'm not sure what went wrong. Do you have a download of this project I can try or what may have gone wrong in the code?

    ReplyDelete
  2. This is the error I get:
    Error 5 'Input0Buffer' does not contain a definition for 'HASHID' and no extension method 'HASHID' accepting a first argument of type 'Input0Buffer' could be found (are you missing a using directive or an assembly reference?) C:\Users\vhatamnegroj1\AppData\Local\Temp\Vsta\SSIS_SC110\VstaiydiGM4V2UmofM9_RmPP_A\Vstah90D4d7kC0KKXHa__j7hYPQ\main.cs 74 85 SC_88ce4e028e754375bd337cfe95a212ea

    ReplyDelete
    Replies
    1. What version of SSIS are you using for this project? Are you sure you added all of the project references needed i.e. System.ServiceModel ?

      Delete
  3. great, works...thanks alot, but I think need upgrade to get all content

    ReplyDelete