Dennis and Jim's Data Engineering Blog: July 2013

Getting an ATOM or RSS feed into SSIS is actually kind of easy, parsing that data out, not so much. In this post I'll describe how to do both using a script component source to get the feed into your data flow.

The control flow, in this example, is going to look something like this:

Figure 1. Control Flow

The Execute SQL Task will be used to get the list of ATOM and RSS feeds we want to download and load them into a SSIS Object Variable. The table we're going to store this list in is:

CREATE TABLE CFG_FEED_LIST

(

FEED_LINK NVARCHAR(1000)

)

INSERT INTO CFG_FEED_LIST(FEED_LINK)

VALUES ('http://www.nytimes.com/services/xml/rss/nyt/HomePage.xml'),

('http://cnn.com/WORLD/blogs/weatherfx/atom.xml'),

('http://feeds.wired.com/wired/index'),

('http://www.bbc.co.uk/blogs/theeditors/atom.xml'),

('http://boston.cbslocal.com/feed/'),

('http://use.perl.org/use.perl.org/index.atom'),

('http://blah/babab')

Notice I also added an erroneous link http://blah/babab in order to demonstrate what happens when you can't connect to a feed. Now we need to create the object variable that will store our list:

Figure 2. Object Variable

Next, we need to configure our Execute SQL Task to populate our object variable:

Figure 3. Execute SQL Task General Screen

Make sure to change the ResultSet property to Full result set. The SQL Statement will select all of our feed into a result set that we can later iterate through, but we need to configure it first on the Result Set screen:

Figure 4. Execute SQL Task Result Set Screen

Make sure to set the result set to the object variable we previously defined, now when this is run the list of ATOM and RSS feeds will be loaded into the variable. With this done we need to dive into our data flow task. The data flow task for this example will look like this:

Figure 5. Data Flow

The script component Feed Source is going to pull the ATOM or RSS feed into our data flow. Drag a script component from the tool box onto the design surface and select source:

Figure 6. Script Component Source

Next, we need to make sure our object variable is available to the script component by adding it to the ReadOnlyVariables property:

Figure 7. Script Component Script Screen

We need to add this variable because we are going to use the values stored in it in our code. With this done, we need to go to the Inputs and Outputs screen to configure the output:

Figure 8. Script Component Input Output Screen

The output consists of HASH_ID DT_WSTR 35, the SHA1 hash value of the feed used to uniquely identify it, RAW_XML DT_NTEXT, the XML of the feed, FEED_TYPE DT_WSTR 10, which tell us if the feed is an ATOM or RSS feed and CALLING_URI DT_WSTR 1000, which is the URI that was used to call the feed. Now we're ready to code, go back to the script screen and click on the Edit Script button. Copy the following code into main.cs (you will have to add System.ServiceModel as a reference):

#region Namespaces

using System;

using System.Data;

using System.Xml;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Windows.Forms;

using System.Text;

using System.Security.Cryptography;

using System.IO;

using System.ServiceModel.Syndication;

using System.Data.OleDb;

#endregion

#region Class

/// <summary>

/// This class is used to extract xml from an RSS or Atom feed and generate a hash value

/// </summary>

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

#region Methods

/// <summary>

/// Generates new output rows

/// </summary>

public override void CreateNewOutputRows()

{

//Holds the XML string

string finalresult;

//Holds Feed Type

string type;

//Holds the calling URI

string feedLink=string.Empty;

//Cast SSIS Object to DataTable

DataTable myTable = new DataTable();

OleDbDataAdapter adapter = new OleDbDataAdapter();

adapter.Fill(myTable, Variables.objFeedList);

//Loop through DataTable for each RSS/Atom Feed

foreach (DataRow row in myTable.Rows)

{

try

{

//Set feedLink to current URI in loop

feedLink = row["FEED_LINK"].ToString();

//Reads XML into string builder from current RSS/Atom link

using (XmlReader reader = XmlReader.Create(feedLink))

{

type = DetermineAtomRss(reader);

XmlDocument doc = new XmlDocument();

doc.Load(reader);

StringWriter sw = new StringWriter();

doc.Save(sw);

finalresult = sw.ToString();

}

//If there is XML to output then add to data flow

if (finalresult != null)

{

//Output the Raw XML and the Hash of the XML

Output0Buffer.AddRow();

Output0Buffer.RAWXML.AddBlobData(Encoding.Unicode.GetBytes(finalresult));

Output0Buffer.HASHID = CreateHash(finalresult);

Output0Buffer.FEEDTYPE = type;

Output0Buffer.CALLINGURI = feedLink;

}

catch (Exception e)

{

failComponent(e.ToString(), feedLink);

}

/// <summary>

/// Produces a SHA1 hash for a string

/// </summary>

/// <param name="data">The string passed to generate a hash</param>

/// <returns>SHA1 hash value for the string passed</returns>

public static string CreateHash(string data)

{

byte[] dataToHash = (new UnicodeEncoding()).GetBytes(data);

SHA1CryptoServiceProvider sha = new SHA1CryptoServiceProvider();

byte[] hashedData = sha.ComputeHash(dataToHash);

RNGCryptoServiceProvider.Create().GetBytes(dataToHash);

string s = Convert.ToBase64String(hashedData, Base64FormattingOptions.None);

return s;

}

/// <summary>

/// Outputs an error message

/// </summary>

/// <param name="errorMsg">The exception message</param>

/// <param name="feedLink">The calling URI that had a failure</param>

private void failComponent(string errorMsg, string feedLink)

{

bool fail = false;

IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;

compMetadata.FireError(1, "Error Loading Feed ("+feedLink+")!", errorMsg, "", 0, out fail);

}

/// <summary>

/// This method determines if the feed is RSS or ATOM

/// </summary>

/// <param name="reader">The XMLReader that contains the feed</param>

/// <returns>A string that is either Unknown, ATOM 1.0 or RSS 2.0</returns>

private string DetermineAtomRss(XmlReader reader)

{

//Default type to Unknown

string type = "Unknown";

//See if the atom formatter can read the XML

Atom10FeedFormatter atom = new Atom10FeedFormatter();

if (atom.CanRead(reader))

{

type = "ATOM 1.0";

}

//See if the RSS formatter can read the XML

Rss20FeedFormatter rss = new Rss20FeedFormatter();

if (rss.CanRead(reader))

{

type = "RSS 2.0";

}

return type;

}

#endregion

}

#endregion

Let's step through and explain some of this code. We passed in our objFeedList variable and need to loop through it to get the feeds we need to call. We can do this by passing the values to a DataTable object and iterating through it in a foreach loop:

DataTable myTable = new DataTable();

OleDbDataAdapter adapter = new OleDbDataAdapter();

adapter.Fill(myTable, Variables.objFeedList);

//Loop through DataTable for each RSS/Atom Feed

foreach (DataRow row in myTable.Rows)

{

....

We then pass the value in the DataTable to a local string variable and grab the feed with the help of an XmlReader:

//Set feedLink to current URI in loop

feedLink = row["FEED_LINK"].ToString();

//Reads XML into a StringWriter from current RSS/Atom link

using (XmlReader reader = XmlReader.Create(feedLink))

{

type = DetermineAtomRss(reader);

XmlDocument doc = new XmlDocument();

doc.Load(reader);

StringWriter sw = new StringWriter();

doc.Save(sw);

finalresult = sw.ToString();

}

We then pass the XmlReader to our DetermineAtomRss method which will return to us Unknown, ATOM 1.0, or RSS 2.0: type = DetermineAtomRss(reader);. We then output our values to the buffer:

//If there is XML to output then add to data flow

if (finalresult != null)

{

//Output the Raw XML and the Hash of the XML

Output0Buffer.AddRow();

Output0Buffer.RAWXML.AddBlobData(Encoding.Unicode.GetBytes(finalresult));

Output0Buffer.HASHID = CreateHash(finalresult);

Output0Buffer.FEEDTYPE = type;

Output0Buffer.CALLINGURI = feedLink;

}

Let's build and save, then slap a data viewer on this and see what we have so far:

Figure 9. Data Viewer ATOM/RSS Feeds

Here you can see we have the unique hash for each feed, the XML of the feed, the type of feed and the calling URI. But you may notice that our data viewer only has 6 records, while our table has 7. This is because one of our feeds is bogus http://blah/babab. Errors connecting to feeds will not hinder the processing of feeds that are good, but we will output the errors so we can see what failed:

Figure 10. Feed Error Message

This data can now be sent to a data destination where the RAW_XML can either be put in a database in a nvarchar(max) column, or in an XML column, or sent to a file destination. However; if you want to parse out the values from the feed and put them into a database you can follow me for a little bit longer in this post.

To parse the data from the feed out so that we can insert into a database we're going to take advantage of a class available in the .net base class library called SyndicationFeed. What's great about this class is that it has a Load method that can take an XmlReader as a parameter and parse out both ATOM and RSS feeds for us. Once we call load all the values are then available to us in properties of the SyndicationFeed. The database schema that is going to act as our data destination is going to look like this:

Figure 11. Destination Database Schema

If you want to use this schema to just stage the data, you can remove the foreign key relationships. I added those so you can see how the data is related. Just about all of the columns in this schema correspond to values stored in the SyndicationFeed. The meta data for this schema can be found in the following matrix:

Database Column	Source(s)	PK/FK	Documentation
SYNDICATION_FEED.HASH_ID	Generated in Script Component – Feed Source	PK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed)
SYNDICATION_FEED.ITEM_ID	Generated in Script Component – Parse XML	PK	This is an incremented integer for the main syndication feed and all of its children items. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_FEED.ID	SyndicationFeed.Id SyndicationFeed.Items.SyndicationItem.Id		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.id(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.id(v=vs.100).aspx
SYNDICATION_FEED.COPYRIGHT	SyndicationFeed.Copyright.Text SyndicationFeed.Items.SyndicationItem.Copyright.Text		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.copyright(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.copyright(v=vs.100).aspx
SYNDICATION_FEED.DESCRIPTION	SyndicationFeed.Description.Text ((TextSyndicationContent)SyndicationFeed.Items.SyndicationItem.Context).Text		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.description(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.content(v=vs.100).aspx
SYNDICATION_FEED.BASE_URI	SyndicationFeed.BaseUri.AbsoluteUri SyndicationFeed.Items.SyndicationItem.BaseUri.AbsoluteUri		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.baseuri(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.baseuri(v=vs.100).aspx
SYNDICATION_FEED.IMAGE_URL	SyndicationFeed.ImageUrl.AbsoluteUri		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.imageurl(v=vs.100).aspx
SYNDICATION_FEED.IMAGE_STORED	Downloaded in Script Component – Parse XML		This is the image file downloaded from the URI stored in SyndicationFeed.ImageURL.AbsoluteUri
SYNDICATION_FEED.GENERATOR	SyndicationFeed.Generator		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.generator(v=vs.100).aspx
SYNDICATION_FEED.LANGUAGE	SyndicationFeed.Language		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.language(v=vs.100).aspx
SYNDICATION_FEED.LAST_UPDATED_TIME	SyndicationFeed.LastUpdatedTime SyndicationFeed.Items.SyndicationItem.LastUpdatedTime		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.lastupdatedtime(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.lastupdatedtime(v=vs.100).aspx
SYNDICATION_FEED.RAW_XML	Downloaded in Script Component – Feed Source		This is the XML from the feed
SYNDICATION_FEED.TITLE	SyndicationFeed.Title.Text SyndicationFeed.Items.SyndicationItem.Title.Text		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.title(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.title(v=vs.100).aspx
SYNDICATION_FEED.FEED_TYPE	Generated in Script Component – Feed Source		This indicates whether or not the feed is ATOM 1.0 or RSS 2.0
SYNDICATION_FEED.CALLING_URI	CFG_FEED_LIST.FEED_LINK		This is the URI that was called to get the XML
SYNDICATION_FEED.PUB_DATE	SyndicationFeed.Items.SyndicationItem.PublishDate		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.publishdate(v=vs.100).aspx
SYNDICATION_FEED.SUMMARY	SyndicationFeed.Items.SyndicationItem.Summary.Text		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.summary(v=vs.100).aspx
SYNDICATION_FEED.SUMMARY_TYPE	SyndicationFeed.Items.SyndicationItem.Summary.Type		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.summary(v=vs.100).aspx
SYNDICATION_FEED.CONTENT_TYPE	SyndicationFeed.Items.SyndicationItem.Content.Type		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.content(v=vs.100).aspx
SYNDICATION_FEED_ELEMENTS.HASH_ID	Generated in Script Component – Feed Source	PK/FK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed), it is derived from the SYNDICATION_FEED table as a foreign key
SYNDICATION_FEED_ELEMENTS.ELEMENT_ID	Generated in Script Component – Parse XML	PK	This is an auto generated integer that is used in the composite primary key to uniquely identify this element
SYNDICATION_FEED_ELEMENTS.ITEM_ID	Generated in Script Component – Parse XML	PK/FK	This is an incremented integer for the main syndication feed that is the parent of this element record. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_FEED_ELEMENTS.OUTER_NAME	SyndicationFeed.ElementExtensions. SyndicationElementExtension.OuterName SyndicationFeed.Items.SyndicationItem.ElementExtensions. SyndicationElementExtension.OuterName		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.elementextensions(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.elementextensions(v=vs.100).aspx
SYNDICATION_FEED_ELEMENTS.OUTER_NAME_SPACE	SyndicationFeed.ElementExtensions. SyndicationElementExtension.OuterNameSpace SyndicationFeed.Items.SyndicationItem.ElementExtensions. SyndicationElementExtension.OuterNameSpace		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.elementextensions(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.elementextensions(v=vs.100).aspx
SYNDICATION_FEED_ELEMENTS.VALUE	SyndicationFeed.ElementExtensions.SyndicationElementExtension.GetObject<string>() SyndicationFeed.Items.SyndicationItem.ElementExtensions. SyndicationElementExtension.GetObject<string>()		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.elementextensions(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.elementextensions(v=vs.100).aspx
SYNDICATION_FEED_ATTRIBUTES.HASH_ID	Generated in Script Component – Feed Source	PK/FK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed), it is derived from the SYNDICATION_FEED table as a foreign key
SYNDICATION_FEED_ATTRIBUTES.ATTRIBUTE_ID	Generated in Script Component – Parse XML	PK	This is an auto generated integer that is used in the composite primary key to uniquely identify this attribute
SYNDICATION_FEED_ATTRIBUTES.ITEM_ID	Generated in Script Component – Parse XML	PK/FK	This is an incremented integer for the main syndication feed that is the parent of this attribute record. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_FEED_ATTRIBUTES.NAME	SyndicationFeed.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Key.Name SyndicationFeed.Items.SyndicationItem.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Key.Name		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.attributeextensions(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.attributeextensions(v=vs.100).aspx
SYNDICATION_FEED_ATTRIBUTES.VALUE	SyndicationFeed.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Value SyndicationFeed.Items.SyndicationItem.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Value		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.attributeextensions(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.attributeextensions(v=vs.100).aspx
SYNDICATION_CONTENT_ATTRIBUTES.HASH_ID	Generated in Script Component – Feed Source	PK/FK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed), it is derived from the SYNDICATION_FEED table as a foreign key
SYNDICATION_CONTENT_ATTRIBUTES.ATTRIBUTE_ID	Generated in Script Component – Parse XML	PK	This is an auto generated integer that is used in the composite primary key to uniquely identify this attribute
SYNDICATION_CONTENT_ATTRIBUTES.ITEM_ID	Generated in Script Component – Parse XML	PK/FK	This is an incremented integer for the main syndication feed that is the parent of this attribute record. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_CONTENT_ATTRIBUTES.NAME	SyndicationFeed.Items.SyndicationItem.Content.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Key.Name		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.content(v=vs.100).aspx
SYNDICATION_CONTENT_ATTRIBUTES.VALUE	SyndicationFeed.Items.SyndicationItem.Content.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Value		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.content(v=vs.100).aspx
SYNDICATION_SUMMARY_ATTRIBUTES.HASH_ID	Generated in Script Component – Feed Source	PK/FK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed), it is derived from the SYNDICATION_FEED table as a foreign key
SYNDICATION_SUMMARY_ATTRIBUTES.ATTRIBUTE_ID	Generated in Script Component – Parse XML	PK	This is an auto generated integer that is used in the composite primary key to uniquely identify this attribute
SYNDICATION_SUMMARY_ATTRIBUTES.ITEM_ID	Generated in Script Component – Parse XML	PK/FK	This is an incremented integer for the main syndication feed that is the parent of this attribute record. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_SUMMARY_ATTRIBUTES.NAME	SyndicationFeed.Items.SyndicationItem.Summary.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Key.Name		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.summary(v=vs.100).aspx
SYNDICATION_SUMMARY_ATTRIBUTES.VALUE	SyndicationFeed.Items.SyndicationItem.Summary.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Value		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.summary(v=vs.100).aspx
SYNDICATION_PERSONS.HASH_ID	Generated in Script Component – Feed Source	PK/FK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed), it is derived from the SYNDICATION_FEED table as a foreign key
SYNDICATION_PERSONS.PERSON_ID	Generated in Script Component – Parse XML	PK	This is an auto generated integer that is used in the composite primary key to uniquely identify this person
SYNDICATION_PERSONS.ITEM_ID	Generated in Script Component – Parse XML	PK/FK	This is an incremented integer for the main syndication feed that is the parent of this attribute record. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_PERSONS.NAME	SyndicationFeed.Authors.SyndicationPerson.Name SyndicationFeed.Contributors.SyndicationPerson.Name SyndicationFeed.Items.SyndicationItem.Authors.SyndicationPerson.Name SyndicationFeed.Items.SyndicationItem.Contributors.SyndicationPerson.Name		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.contributors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.contributors(v=vs.100).aspx
SYNDICATION_PERSONS.EMAIL	SyndicationFeed.Authors.SyndicationPerson.Email SyndicationFeed.Contributors.SyndicationPerson.Email SyndicationFeed.Items.SyndicationItem.Authors.SyndicationPerson.Email SyndicationFeed.Items.SyndicationItem.Contributors.SyndicationPerson.Email		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.contributors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.contributors(v=vs.100).aspx
SYNDICATION_PERSONS.URI	SyndicationFeed.Authors.SyndicationPerson.Uri SyndicationFeed.Contributors.SyndicationPerson. Uri SyndicationFeed.Items.SyndicationItem.Authors.SyndicationPerson. Uri SyndicationFeed.Items.SyndicationItem.Contributors.SyndicationPerson. Uri		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.contributors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.contributors(v=vs.100).aspx
SYNDICATION_PERSONS.TYPE	Generated in Script Component – Parse XML		This can either be Author or Contributor. It determines if this record came from an author collection or a contributor collection
SYNDICATION_PERSONS_ELEMENTS.HASH_ID	Generated in Script Component – Feed Source	PK/FK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed), it is derived from the SYNDICATION_FEED table as a foreign key
SYNDICATION_PERSONS_ELEMENTS.PERSON_ID	Generated in Script Component – Parse XML	PK/FK	This is an auto generated integer that is used in the composite primary key to uniquely identify the person that is the parent of this element
SYNDICATION_PERSONS_ELEMENTS.ELEMENT_ID	Generated in Script Component – Parse XML	PK	This is an auto generated integer that is used in the composite primary key to uniquely identify this element
SYNDICATION_PERSONS_ELEMENTS.ITEM_ID	Generated in Script Component – Parse XML	PK/FK	This is an incremented integer for the main syndication feed that is the parent of this element record. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_PERSONS_ELEMENTS.OUTER_NAME	SyndicationFeed.Authors.SyndicationPerson.ElementExtensions. SyndicationElementExtension.OuterName SyndicationFeed.Contributors.SyndicationPerson.ElementExtensions. SyndicationElementExtension.OuterName SyndicationFeed.Items.SyndicationItem.Authors.SyndicationPerson.ElementExtensions. SyndicationElementExtension.OuterName SyndicationFeed.Items.SyndicationItem.Contributors.SyndicationPerson.ElementExtensions. SyndicationElementExtension.OuterName		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.contributors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.contributors(v=vs.100).aspx
SYNDICATION_PERSONS_ELEMENTS.OUTER_NAME_SPACE	SyndicationFeed.Authors.SyndicationPerson.ElementExtensions. SyndicationElementExtension.OuterNameSpace SyndicationFeed.Contributors.SyndicationPerson.ElementExtensions. SyndicationElementExtension. OuterNameSpace SyndicationFeed.Items.SyndicationItem.Authors.SyndicationPerson.ElementExtensions. SyndicationElementExtension. OuterNameSpace SyndicationFeed.Items.SyndicationItem.Contributors.SyndicationPerson.ElementExtensions. SyndicationElementExtension. OuterNameSpace		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.contributors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.contributors(v=vs.100).aspx
SYNDICATION_PERSONS_ELEMENTS.VALUE	SyndicationFeed.Authors.SyndicationPerson.ElementExtensions. SyndicationElementExtension.GetObject<string>() SyndicationFeed.Contributors.SyndicationPerson.ElementExtensions. SyndicationElementExtension.GetObject<string>() SyndicationFeed.Items.SyndicationItem.Authors.SyndicationPerson.ElementExtensions. SyndicationElementExtension.GetObject<string>() SyndicationFeed.Items.SyndicationItem.Contributors.SyndicationPerson.ElementExtensions. SyndicationElementExtension.GetObject<string>()		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.contributors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.contributors(v=vs.100).aspx
SYNDICATION_PERSONS_ATTRIBUTES.HASH_ID	Generated in Script Component – Feed Source	PK/FK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed), it is derived from the SYNDICATION_FEED table as a foreign key
SYNDICATION_PERSONS_ATTRIBUTES.PERSON_ID	Generated in Script Component – Parse XML	PK/FK	This is an auto generated integer that is used in the composite primary key to uniquely identify the person that is the parent of this attribute
SYNDICATION_PERSONS_ ATTRIBUTES.ATTRIBUTE_ID	Generated in Script Component – Parse XML	PK	This is an auto generated integer that is used in the composite primary key to uniquely identify this attribute
SYNDICATION_PERSONS_ ATTRIBUTES.ITEM_ID	Generated in Script Component – Parse XML	PK/FK	This is an incremented integer for the main syndication feed that is the parent of this attribute record. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_PERSONS_ ATTRIBUTES.NAME	SyndicationFeed.Authors.SyndicationPerson.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Key.Name SyndicationFeed.Contributors.SyndicationPerson. AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Key.Name SyndicationFeed.Items.SyndicationItem.Authors.SyndicationPerson. AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Key.Name SyndicationFeed.Items.SyndicationItem.Contributors.SyndicationPerson. AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Key.Name		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.contributors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.contributors(v=vs.100).aspx
SYNDICATION_PERSONS_ ATTRIBUTES.VALUE	SyndicationFeed.Authors.SyndicationPerson.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Value SyndicationFeed.Contributors.SyndicationPerson. AttributeExtensions.KeyValuePair<XmlQualifiedName, string>. Value SyndicationFeed.Items.SyndicationItem.Authors.SyndicationPerson. AttributeExtensions.KeyValuePair<XmlQualifiedName, string>. Value SyndicationFeed.Items.SyndicationItem.Contributors.SyndicationPerson. AttributeExtensions.KeyValuePair<XmlQualifiedName, string>. Value		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.contributors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.authors(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.contributors(v=vs.100).aspx
SYNDICATION_CATEGORIES.HASH_ID	Generated in Script Component – Feed Source	PK/FK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed), it is derived from the SYNDICATION_FEED table as a foreign key
SYNDICATION_CATEGORIES.CATEGORY_ID	Generated in Script Component – Parse XML	PK	This is an auto generated integer that is used in the composite primary key to uniquely identify the category
SYNDICATION_CATEGORIES.ITEM_ID	Generated in Script Component – Parse XML	PK/FK	This is an incremented integer for the main syndication feed that is the parent of this category record. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_CATEGORIES.LABEL	SyndicationFeed.Categories.SyndicationCategory.Label SyndicationFeed.Items.SyndicationItem.Categories.SyndicationCategory.Label		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.categories(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.categories(v=vs.100).aspx
SYNDICATION_CATEGORIES.NAME	SyndicationFeed.Categories.SyndicationCategory.Name SyndicationFeed.Items.SyndicationItem.Categories.SyndicationCategory.Name		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.categories(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.categories(v=vs.100).aspx
SYNDICATION_CATEGORIES.SCHEME	SyndicationFeed.Categories.SyndicationCategory.Scheme SyndicationFeed.Items.SyndicationItem.Categories.SyndicationCategory.Scheme		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.categories(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.categories(v=vs.100).aspx
SYNDICATION_CATEGORIES_ELEMENTS.HASH_ID	Generated in Script Component – Feed Source	PK/FK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed), it is derived from the SYNDICATION_FEED table as a foreign key
SYNDICATION_CATEGORIES_ELEMENTS.CATEGORY_ID	Generated in Script Component – Parse XML	PK/FK	This is an auto generated integer that is used in the composite primary key to uniquely identify the category that is the parent of this element
SYNDICATION_CATEGORIES_ELEMENTS.ELEMENT_ID	Generated in Script Component – Parse XML	PK	This is an auto generated integer that is used in the composite primary key to uniquely identify this element
SYNDICATION_CATEGORIES_ELEMENTS.ITEM_ID	Generated in Script Component – Parse XML	PK/FK	This is an incremented integer for the main syndication feed that is the parent of this element record. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_CATEGORIES_ELEMENTS.OUTER_NAME	SyndicationFeed.Categories.SyndicationCategory.ElementExtensions. SyndicationElementExtension.OuterName SyndicationFeed.Items.SyndicationItem.Categories.SyndicationCategory.ElementExtensions. SyndicationElementExtension.OuterName	PK/FK	http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.categories(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.categories(v=vs.100).aspx
SYNDICATION_CATEGORIES_ELEMENTS.OUTER_NAME_SPACE	SyndicationFeed.Categories.SyndicationCategory.ElementExtensions. SyndicationElementExtension.OuterNameSpace SyndicationFeed.Items.SyndicationItem.Categories.SyndicationCategory.ElementExtensions. SyndicationElementExtension.OuterNameSpace	PK/FK	http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.categories(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.categories(v=vs.100).aspx
SYNDICATION_CATEGORIES_ELEMENTS.VALUE	SyndicationFeed.Categories.SyndicationCategory.ElementExtensions. SyndicationElementExtension.GetObject<string>() SyndicationFeed.Items.SyndicationItem. Categories.SyndicationCategory.ElementExtensions. SyndicationElementExtension.GetObject<string>()		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.categories(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.categories(v=vs.100).aspx
SYNDICATION_CATEGORIES_ATTRIBUTES.HASH_ID	Generated in Script Component – Feed Source	PK/FK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed), it is derived from the SYNDICATION_FEED table as a foreign key
SYNDICATION_CATEGORIES_ATTRIBUTES.CATEGORY_ID	Generated in Script Component – Parse XML	PK/FK	This is an auto generated integer that is used in the composite primary key to uniquely identify the category that is the parent of this attribute
SYNDICATION_CATEGORIES_ATTRIBUTES.ATTRIBUTE_ID	Generated in Script Component – Parse XML	PK	This is an auto generated integer that is used in the composite primary key to uniquely identify this attribute
SYNDICATION_CATEGORIES_ATTRIBUTES.ITEM_ID	Generated in Script Component – Parse XML	PK/FK	This is an incremented integer for the main syndication feed that is the parent of this attribute record. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_CATEGORIES_ATTRIBUTES.NAME	SyndicationFeed.Categories.SyndicationCategory.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Key.Name SyndicationFeed.Items.SyndicationItem.Categories.SyndicationCategory.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Key.Name		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.categories(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.categories(v=vs.100).aspx
SYNDICATION_CATEGORIES_ATTRIBUTES.VALUE	SyndicationFeed.Categories.SyndicationCategory.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Value SyndicationFeed.Items.SyndicationItem.Categories.SyndicationCategory.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Value		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.categories(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.categories(v=vs.100).aspx
SYNDICATION_LINKS.HASH_ID	Generated in Script Component – Feed Source	PK/FK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed), it is derived from the SYNDICATION_FEED table as a foreign key
SYNDICATION_LINKS.LINK_ID	Generated in Script Component – Parse XML	PK	This is an auto generated integer that is used in the composite primary key to uniquely identify the link
SYNDICATION_LINKS.ITEM_ID	Generated in Script Component – Parse XML	PK/FK	This is an incremented integer for the main syndication feed that is the parent of this link record. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_LINKS.BASE_URI	SyndicationFeed.Links.SyndicationLink.BaseUri.AbsoluteUri SyndicationFeed.Items.SyndicationItem.Links.SyndicationLink.BaseUri.AbsoluteUri		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.links(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.links(v=vs.100).aspx
SYNDICATION_LINKS.LENGTH	SyndicationFeed.Links.SyndicationLink.Length SyndicationFeed.Items.SyndicationItem.Links.SyndicationLink.Length		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.links(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.links(v=vs.100).aspx
SYNDICATION_LINKS.MEDIA_TYPE	SyndicationFeed.Links.SyndicationLink.MediaType SyndicationFeed.Items.SyndicationItem.Links.SyndicationLink. MediaType		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.links(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.links(v=vs.100).aspx
SYNDICATION_LINKS.RELATIONSHIP_TYPE	SyndicationFeed.Links.SyndicationLink.RelationshipType SyndicationFeed.Items.SyndicationItem.Links.SyndicationLink. RelationshipType		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.links(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.links(v=vs.100).aspx
SYNDICATION_LINKS.TITLE	SyndicationFeed.Links.SyndicationLink.Title SyndicationFeed.Items.SyndicationItem.Links.SyndicationLink.Title		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.links(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.links(v=vs.100).aspx
SYNDICATION_LINKS.URI	SyndicationFeed.Links.SyndicationLink.Uri.AbsoluteUri SyndicationFeed.Items.SyndicationItem.Links.SyndicationLink.Uri.AbsoluteUri		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.links(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.links(v=vs.100).aspx
SYNDICATION_LINKS_ELEMENTS.HASH_ID	Generated in Script Component – Feed Source	PK/FK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed), it is derived from the SYNDICATION_FEED table as a foreign key
SYNDICATION_LINKS_ELEMENTS.LINK_ID	Generated in Script Component – Parse XML	PK/FK	This is an auto generated integer that is used in the composite primary key to uniquely identify the link that is the parent of this element
SYNDICATION_LINKS_ELEMENTS.ELEMENT_ID	Generated in Script Component – Parse XML	PK	This is an auto generated integer that is used in the composite primary key to uniquely identify this element
SYNDICATION_LINKS_ELEMENTS.ITEM_ID	Generated in Script Component – Parse XML	PK/FK	This is an incremented integer for the main syndication feed that is the parent of this element record. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_LINKS_ELEMENTS.OUTER_NAME	SyndicationFeed.Links.SyndicationLink.ElementExtensions. SyndicationElementExtension.OuterName SyndicationFeed.Items.SyndicationItem.Links.SyndicationLink.ElementExtensions. SyndicationElementExtension.OuterName		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.links(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.links(v=vs.100).aspx
SYNDICATION_LINKS_ELEMENTS.OUTER_NAME_SPACE	SyndicationFeed.Links.SyndicationLink.ElementExtensions. SyndicationElementExtension.OuterNameSpace SyndicationFeed.Items.SyndicationItem.Links.SyndicationLink.ElementExtensions. SyndicationElementExtension.OuterNameSpace		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.links(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.links(v=vs.100).aspx
SYNDICATION_LINKS_ATTRIBUTES.HASH_ID	Generated in Script Component – Feed Source	PK/FK	This is a SHA1 hash for the entire XML document(ATOM/RSS feed), it is derived from the SYNDICATION_FEED table as a foreign key
SYNDICATION_LINKS_ATTRIBUTES.LINK_ID	Generated in Script Component – Parse XML	PK/FK	This is an auto generated integer that is used in the composite primary key to uniquely identify the link that is the parent of this attribute
SYNDICATION_LINKS_ATTRIBUTES.ATTRIBUTE_ID	Generated in Script Component – Parse XML	PK	This is an auto generated integer that is used in the composite primary key to uniquely identify this attribute
SYNDICATION_LINKS_ATTRIBUTES.ITEM_ID	Generated in Script Component – Parse XML	PK/FK	This is an incremented integer for the main syndication feed that is the parent of this attribute record. The main feed will always have a 0 for item id, while items will begin with 1 and up
SYNDICATION_LINKS_ATTRIBUTES.NAME	SyndicationFeed.Links.SyndicationLink.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Key.Name SyndicationFeed.Items.SyndicationItem.Links.SyndicationLink.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Key.Name		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.links(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.links(v=vs.100).aspx
SYNDICATION_LINKS_ATTRIBUTES.VALUE	SyndicationFeed.Links.SyndicationLink.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Value SyndicationFeed.Items.SyndicationItem.Links.SyndicationLink.AttributeExtensions.KeyValuePair<XmlQualifiedName, string>.Value		http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.links(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationitem.links(v=vs.100).aspx

-->Create Table Scripts

* TABLE: SYNDICATION_CATEGORIES

CREATE TABLE SYNDICATION_CATEGORIES(

HASH_ID nvarchar(35) NOT NULL,

CATEGORY_ID int NOT NULL,

ITEM_ID int NOT NULL,

LABEL nvarchar(255) NULL,

NAME nvarchar(255) NULL,

SCHEME nvarchar(255) NULL,

CONSTRAINT PK_SYNDICATION_CATEGORIES PRIMARY KEY CLUSTERED (HASH_ID, CATEGORY_ID, ITEM_ID)

)

IF OBJECT_ID('SYNDICATION_CATEGORIES') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_CATEGORIES >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_CATEGORIES >>>'

* TABLE: SYNDICATION_CATEGORIES_ATTRIBUTES

CREATE TABLE SYNDICATION_CATEGORIES_ATTRIBUTES(

HASH_ID nvarchar(35) NOT NULL,

CATEGORY_ID int NOT NULL,

ATTRIBUTE_ID int NOT NULL,

ITEM_ID int NOT NULL,

NAME nvarchar(255) NULL,

VALUE nvarchar(max) NULL,

CONSTRAINT PK_SYNDICATION_CATEGORIES_ATTRIBUTES PRIMARY KEY CLUSTERED (HASH_ID, CATEGORY_ID, ATTRIBUTE_ID, ITEM_ID)

)

IF OBJECT_ID('SYNDICATION_CATEGORIES_ATTRIBUTES') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_CATEGORIES_ATTRIBUTES >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_CATEGORIES_ATTRIBUTES >>>'

* TABLE: SYNDICATION_CATEGORIES_ELEMENTS

CREATE TABLE SYNDICATION_CATEGORIES_ELEMENTS(

HASH_ID nvarchar(35) NOT NULL,

CATEGORY_ID int NOT NULL,

ELEMENT_ID int NOT NULL,

ITEM_ID int NOT NULL,

OUTER_NAME nvarchar(288) NULL,

OUTER_NAME_SPACE nvarchar(255) NULL,

VALUE nvarchar(max) NULL,

CONSTRAINT PK_SYNDICATION_CATEGORIES_ELEMENTS PRIMARY KEY CLUSTERED (HASH_ID, CATEGORY_ID, ITEM_ID, ELEMENT_ID)

)

IF OBJECT_ID('SYNDICATION_CATEGORIES_ELEMENTS') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_CATEGORIES_ELEMENTS >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_CATEGORIES_ELEMENTS >>>'

* TABLE: SYNDICATION_CONTENT_ATTRIBUTES

CREATE TABLE SYNDICATION_CONTENT_ATTRIBUTES(

HASH_ID nvarchar(35) NOT NULL,

ATTRIBUTE_ID int NOT NULL,

ITEM_ID int NOT NULL,

NAME nvarchar(255) NULL,

VALUE nvarchar(max) NULL,

CONSTRAINT PK_SYNDICATION_CONTENT_ATTRIBUTES PRIMARY KEY CLUSTERED (HASH_ID, ITEM_ID, ATTRIBUTE_ID)

)

IF OBJECT_ID('SYNDICATION_CONTENT_ATTRIBUTES') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_CONTENT_ATTRIBUTES >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_CONTENT_ATTRIBUTES >>>'

* TABLE: SYNDICATION_FEED

CREATE TABLE SYNDICATION_FEED(

HASH_ID nvarchar(35) NOT NULL,

ITEM_ID int NOT NULL,

ID nvarchar(255) NULL,

DESCRIPTION nvarchar(max) NULL,

BASE_URI nvarchar(1000) NULL,

IMAGE_URL nvarchar(1000) NULL,

IMAGE_STORED image NULL,

GENERATOR nvarchar(1000) NULL,

LANGUAGE nvarchar(1000) NULL,

LAST_UPDATED_TIME nvarchar(50) NULL,

RAW_XML xml NULL,

TITLE nvarchar(1000) NULL,

FEED_TYPE nvarchar(10) NULL,

CALLING_URI nvarchar(1000) NULL,

PUB_DATE nvarchar(50) NULL,

SUMMARY nvarchar(max) NULL,

SUMMARY_TYPE nvarchar(255) NULL,

CONTENT_TYPE nvarchar(255) NULL,

CONSTRAINT PK_SYNDICATION_FEED PRIMARY KEY CLUSTERED (HASH_ID, ITEM_ID)

)

IF OBJECT_ID('SYNDICATION_FEED') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_FEED >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_FEED >>>'

* TABLE: SYNDICATION_FEED_ATTRIBUTES

CREATE TABLE SYNDICATION_FEED_ATTRIBUTES(

HASH_ID nvarchar(35) NOT NULL,

ATTRIBUTE_ID int NOT NULL,

ITEM_ID int NOT NULL,

NAME nvarchar(255) NULL,

VALUE nvarchar(max) NULL,

CONSTRAINT PK_SYNDICATION_FEED_ATTRIBUTES PRIMARY KEY CLUSTERED (HASH_ID, ATTRIBUTE_ID, ITEM_ID)

)

IF OBJECT_ID('SYNDICATION_FEED_ATTRIBUTES') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_FEED_ATTRIBUTES >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_FEED_ATTRIBUTES >>>'

* TABLE: SYNDICATION_FEED_ELEMENTS

CREATE TABLE SYNDICATION_FEED_ELEMENTS(

HASH_ID nvarchar(35) NOT NULL,

ELEMENT_ID int NOT NULL,

ITEM_ID int NOT NULL,

OUTER_NAME nvarchar(255) NULL,

OUTER_NAME_SPACE nvarchar(255) NULL,

VALUE nvarchar(max) NULL,

CONSTRAINT PK_SYNDICATION_FEED_ELEMENTS PRIMARY KEY CLUSTERED (HASH_ID, ELEMENT_ID, ITEM_ID)

)

IF OBJECT_ID('SYNDICATION_FEED_ELEMENTS') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_FEED_ELEMENTS >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_FEED_ELEMENTS >>>'

* TABLE: SYNDICATION_LINKS

CREATE TABLE SYNDICATION_LINKS(

HASH_ID nvarchar(35) NOT NULL,

LINK_ID int NOT NULL,

ITEM_ID int NOT NULL,

BASE_URI nvarchar(1000) NULL,

LENGTH int NULL,

MEDIA_TYPE nvarchar(50) NULL,

RELATIONSHIP_TYPE nvarchar(50) NULL,

TITLE nvarchar(1000) NULL,

URI nvarchar(1000) NULL,

CONSTRAINT PK_SYNDICATION_LINKS PRIMARY KEY CLUSTERED (HASH_ID, LINK_ID, ITEM_ID)

)

IF OBJECT_ID('SYNDICATION_LINKS') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_LINKS >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_LINKS >>>'

* TABLE: SYNDICATION_LINKS_ATTRIBUTES

CREATE TABLE SYNDICATION_LINKS_ATTRIBUTES(

HASH_ID nvarchar(35) NOT NULL,

LINK_ID int NOT NULL,

ATTRIBUTE_ID int NOT NULL,

ITEM_ID int NOT NULL,

NAME nvarchar(255) NULL,

VALUE nvarchar(max) NULL,

CONSTRAINT PK_SYNDICATION_LINKS_ATTRIBUTES PRIMARY KEY CLUSTERED (HASH_ID, LINK_ID, ATTRIBUTE_ID, ITEM_ID)

)

IF OBJECT_ID('SYNDICATION_LINKS_ATTRIBUTES') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_LINKS_ATTRIBUTES >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_LINKS_ATTRIBUTES >>>'

* TABLE: SYNDICATION_LINKS_ELEMENTS

CREATE TABLE SYNDICATION_LINKS_ELEMENTS(

HASH_ID nvarchar(35) NOT NULL,

LINK_ID int NOT NULL,
ELEMENT_ID int NOT NULL,

ITEM_ID int NOT NULL,

OUTER_NAME nvarchar(255) NULL,

OUTER_NAME_SPACE nvarchar(255) NULL,

VALUE nvarchar(max) NULL,

CONSTRAINT PK_SYNDICATION_LINKS_ELEMENTS PRIMARY KEY CLUSTERED (HASH_ID, LINK_ID, ELEMENT_ID, ITEM_ID)

)

IF OBJECT_ID('SYNDICATION_LINKS_ELEMENTS') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_LINKS_ELEMENTS >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_LINKS_ELEMENTS >>>'

* TABLE: SYNDICATION_PERSONS

CREATE TABLE SYNDICATION_PERSONS(

HASH_ID nvarchar(35) NOT NULL,

PERSON_ID int NOT NULL,

ITEM_ID int NOT NULL,

NAME nvarchar(255) NULL,

EMAIL nvarchar(255) NULL,

URI nvarchar(255) NULL,

TYPE nvarchar(20) NULL,

CONSTRAINT PK_SYNDICATION_PERSONS PRIMARY KEY CLUSTERED (HASH_ID, PERSON_ID, ITEM_ID)

)

IF OBJECT_ID('SYNDICATION_PERSONS') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_PERSONS >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_PERSONS >>>'

* TABLE: SYNDICATION_PERSONS_ATTRIBUTES

CREATE TABLE SYNDICATION_PERSONS_ATTRIBUTES(

HASH_ID nvarchar(35) NOT NULL,

PERSON_ID int NOT NULL,

ATTRIBUTE_ID int NOT NULL,

ITEM_ID int NOT NULL,

NAME nvarchar(255) NULL,

VALUE nvarchar(max) NULL,

CONSTRAINT PK_SYNDICATION_PERSONS_ATTRIBUTES PRIMARY KEY CLUSTERED (HASH_ID, PERSON_ID, ATTRIBUTE_ID, ITEM_ID)

)

IF OBJECT_ID('SYNDICATION_PERSONS_ATTRIBUTES') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_PERSONS_ATTRIBUTES >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_PERSONS_ATTRIBUTES >>>'

* TABLE: SYNDICATION_PERSONS_ELEMENTS

CREATE TABLE SYNDICATION_PERSONS_ELEMENTS(

HASH_ID nvarchar(35) NOT NULL,

PERSON_ID int NOT NULL,

ELEMENT_ID int NOT NULL,

ITEM_ID int NOT NULL,

OUTER_NAME nvarchar(255) NULL,

OUTER_NAME_SPACE nvarchar(255) NULL,

VALUE nvarchar(max) NULL,

CONSTRAINT PK_SYNDICATION_PERSONS_ELEMENTS PRIMARY KEY CLUSTERED (HASH_ID, PERSON_ID, ELEMENT_ID, ITEM_ID)

)

IF OBJECT_ID('SYNDICATION_PERSONS_ELEMENTS') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_PERSONS_ELEMENTS >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_PERSONS_ELEMENTS >>>'

* TABLE: SYNDICATION_SUMMARY_ATTRIBUTES

CREATE TABLE SYNDICATION_SUMMARY_ATTRIBUTES(

HASH_ID nvarchar(35) NOT NULL,

ATTRIBUTE_ID int NOT NULL,

ITEM_ID int NOT NULL,

NAME nvarchar(255) NULL,

VALUE nvarchar(max) NULL,

CONSTRAINT PK_SYNDICATION_SUMMARY_ATTRIBUTES PRIMARY KEY NONCLUSTERED (HASH_ID, ITEM_ID, ATTRIBUTE_ID)

)

IF OBJECT_ID('SYNDICATION_SUMMARY_ATTRIBUTES') IS NOT NULL

PRINT '<<< CREATED TABLE SYNDICATION_SUMMARY_ATTRIBUTES >>>'

ELSE

PRINT '<<< FAILED CREATING TABLE SYNDICATION_SUMMARY_ATTRIBUTES >>>'

* TABLE: SYNDICATION_CATEGORIES

ALTER TABLE SYNDICATION_CATEGORIES ADD CONSTRAINT FK_SYNDICATION_CATEGORIES_HASH_ID_ITEM_ID

FOREIGN KEY (HASH_ID, ITEM_ID)

REFERENCES SYNDICATION_FEED(HASH_ID, ITEM_ID)

* TABLE: SYNDICATION_CATEGORIES_ATTRIBUTES

ALTER TABLE SYNDICATION_CATEGORIES_ATTRIBUTES ADD CONSTRAINT FK_SYNDICATION_CATEGORIES_ATTRIBUTES_HASH_ID_ITEM_ID

FOREIGN KEY (HASH_ID, CATEGORY_ID, ITEM_ID)

REFERENCES SYNDICATION_CATEGORIES(HASH_ID, CATEGORY_ID, ITEM_ID)

* TABLE: SYNDICATION_CATEGORIES_ELEMENTS

ALTER TABLE SYNDICATION_CATEGORIES_ELEMENTS ADD CONSTRAINT FK_SYNDICATION_CATEGORIES_ELEMENTS_HASH_ID_ITEM_ID

FOREIGN KEY (HASH_ID, CATEGORY_ID, ITEM_ID)

REFERENCES SYNDICATION_CATEGORIES(HASH_ID, CATEGORY_ID, ITEM_ID)

* TABLE: SYNDICATION_CONTENT_ATTRIBUTES

ALTER TABLE SYNDICATION_CONTENT_ATTRIBUTES ADD CONSTRAINT FK_SYNDICATION_CONTENT_ATTRIBUTES_HASH_ID_ITEM_ID

FOREIGN KEY (HASH_ID, ITEM_ID)

REFERENCES SYNDICATION_FEED(HASH_ID, ITEM_ID)

* TABLE: SYNDICATION_FEED_ATTRIBUTES

ALTER TABLE SYNDICATION_FEED_ATTRIBUTES ADD CONSTRAINT FK_SYNDICATION_FEED_ATTRIBUTES_HASH_ID_ITEM_ID

FOREIGN KEY (HASH_ID, ITEM_ID)

REFERENCES SYNDICATION_FEED(HASH_ID, ITEM_ID)

* TABLE: SYNDICATION_FEED_ELEMENTS

ALTER TABLE SYNDICATION_FEED_ELEMENTS ADD CONSTRAINT FK_SYNDICATION_FEED_ELEMENTS_HASH_ID_ITEM_ID

FOREIGN KEY (HASH_ID, ITEM_ID)

REFERENCES SYNDICATION_FEED(HASH_ID, ITEM_ID)

* TABLE: SYNDICATION_LINKS

ALTER TABLE SYNDICATION_LINKS ADD CONSTRAINT FK_SYNDICATION_LINKS_HASH_ID_ITEM_ID

FOREIGN KEY (HASH_ID, ITEM_ID)

REFERENCES SYNDICATION_FEED(HASH_ID, ITEM_ID)

* TABLE: SYNDICATION_LINKS_ATTRIBUTES

ALTER TABLE SYNDICATION_LINKS_ATTRIBUTES ADD CONSTRAINT FK_SYNDICATION_LINKS_ATTRIBUTES_HASH_ID_ITEM_ID

FOREIGN KEY (HASH_ID, LINK_ID, ITEM_ID)

REFERENCES SYNDICATION_LINKS(HASH_ID, LINK_ID, ITEM_ID)

* TABLE: SYNDICATION_LINKS_ELEMENTS

ALTER TABLE SYNDICATION_LINKS_ELEMENTS ADD CONSTRAINT FK_SYNDICATION_LINKS_ELEMENTS_HASH_ID_ITEM_ID

FOREIGN KEY (HASH_ID, LINK_ID, ITEM_ID)

REFERENCES SYNDICATION_LINKS(HASH_ID, LINK_ID, ITEM_ID)

* TABLE: SYNDICATION_PERSONS

ALTER TABLE SYNDICATION_PERSONS ADD CONSTRAINT FK_SYNDICATION_PERSONS_HASH_ID_ITEM_ID

FOREIGN KEY (HASH_ID, ITEM_ID)

REFERENCES SYNDICATION_FEED(HASH_ID, ITEM_ID)

* TABLE: SYNDICATION_PERSONS_ATTRIBUTES

ALTER TABLE SYNDICATION_PERSONS_ATTRIBUTES ADD CONSTRAINT FK_SYNDICATION_FEED_PERSONS_ATTRIBUTES_HASH_ID_ITEM_ID

FOREIGN KEY (HASH_ID, PERSON_ID, ITEM_ID)

REFERENCES SYNDICATION_PERSONS(HASH_ID, PERSON_ID, ITEM_ID)

* TABLE: SYNDICATION_PERSONS_ELEMENTS

ALTER TABLE SYNDICATION_PERSONS_ELEMENTS ADD CONSTRAINT FK_SYNDICATION_FEED_PERSONS_ELEMENTS_HASH_ID_ITEM_ID

FOREIGN KEY (HASH_ID, PERSON_ID, ITEM_ID)

REFERENCES SYNDICATION_PERSONS(HASH_ID, PERSON_ID, ITEM_ID)

* TABLE: SYNDICATION_SUMMARY_ATTRIBUTES

ALTER TABLE SYNDICATION_SUMMARY_ATTRIBUTES ADD CONSTRAINT FK_SYNDICATION_SUMMARY_ATTRIBUTES_HASH_ID_ITEM_ID

FOREIGN KEY (HASH_ID, ITEM_ID)

REFERENCES SYNDICATION_FEED(HASH_ID, ITEM_ID)

Now that we have the tables created we can continue with this package. The next thing to look at is the Look up transformation. This is how we are going to filter out feeds we have already processed. If we poll the feed and nothing has changed, we don't need to parse everything out again. We determine if we already loaded this feed by comparing Hashes already stored in the database:

Figure 12. Lookup Transformation Connection Screen

with the Hash coming in the data flow :

Figure 13. Lookup Transformation Columns Screen

Because we only care about feeds we don't already have in the database, we need to send rows with no matching entries to the no match output:

Figure 14. Lookup Transformation General Screen

With this out of the way, we can focus on how the script component is going to parse the XML and get it into this schema. Drag a script component onto the design surface and select Transformation, make sure to connect the data flow path Lookup No Match Output to this:

Figure 15. Script Component Type

On the Input Columns screen make sure to select all the columns we have in the current data flow:

Figure 16. Script Component Input Columns Screen

On the Inputs and Outputs screen we need to remove the default Output since we are going to create our own set of them that aren't tied to the Input:

Figure 16. Script Component Inputs and Outputs Screen

Here is the makeup of each of the output's output columns:

Syndication_Feed_Out

ITEM_ID DT_I4
ID DT_I4
COPYRIGHT DT_WSTR 255
DESCRIPTION DT_NTEXT
BASE_URI DT_WSTR 1000
IMAGE_URL DT_WSTR 1000
IMAGE_STORED DT_IMAGE
GENERATOR DT_WSTR 1000
LANGUAGE DT_WSTR 1000
LAST_UPDATED_TIME DT_WSTR 50
TITLE DT_WSTR 1000
FEED_TYPE DT_WSTR 10
HASH_ID DT_WSTR 35
RAW_XML DT_NTEXT
CALLING_URI DT_WSTR 1000
PUB_DATE DT_WSTR 50
SUMMARY DT_NTEXT
SUMMARY_TYPE DT_WSTR 255
CONTENT_TYPE DT_WSTR 255

Syndication_Summary_Attributes_Out

HASH_ID DT_WSTR 35
ATTRIBUTE_ID DT_I4
ITEM_ID DT_I4
NAME DT_WSTR 255
VALUE DT_NTEXT

Syndication_Content_Attributes_Out

HASH_ID DT_WSTR 35
ATTRIBUTE_ID DT_I4
ITEM_ID DT_I4
NAME DT_WSTR 255
VALUE DT_NTEXT

Syndication_Links_Elements_Out

HASH_ID DT_WSTR 35
LINK_ID DT_I4
ELEMENT_ID DT_I4
ITEM_ID DT_I4
OUTER_NAME DT_WSTR 255
OUTER_NAME_SPACE DT_WSTR 255
VALUE DT_NTEXT

Syndication_Links_Attributes_Out

HASH_ID DT_WSTR 35
LINK_ID DT_I4
ATTRIBUTE_ID DT_I4
ITEM_ID DT_I4
NAME DT_WSTR 255
VALUE DT_NTEXT

Syndication_Links_Out

HASH_ID DT_WSTR 35
LINK_ID DT_I4
ITEM_ID DT_I4
LENGTH DT_I8
MEDIA_TYPE DT_WSTR 50
RELATIONSHIP_TYPE DT_WSTR 50
TITLE DT_WSTR 1000
URI DT_WSTR 1000

Syndication_Categories_Attributes_Out

HASH_ID DT_WSTR 35
CATEGORY_ID DT_I4
ATTRIBUTE_ID DT_I4
ITEM_ID DT_I4
NAME DT_WSTR 255
VALUE DT_WSTR 255

Syndication_Categories_Elements_Out

HASH_ID DT_WSTR 35
CATEGORY_ID DT_I4
ELEMENT_ID DT_I4
ITEM_ID DT_I4
OUTER_NAME DT_WSTR 255
OUTER_NAME_SPACE DT_WSTR 255
VALUE DT_NTEXT

Syndication_Categories_Out

HASH_ID DT_WSTR 35
CATEGORY_ID DT_I4
ITEM_ID DT_I4
LABEL DT_WSTR 255
NAME DT_WSTR 255
SCHEME DT_WSTR 255

Syndication_Persons_Elements_Out

HASH_ID DT_WSTR 35
PERSON_ID DT_I4
ELEMENT_ID DT_I4
ITEM_ID DT_I4
OUTER_NAME DT_WSTR 255
OUTER_NAME_SPACE DT_WSTR 255
VALUE DT_NTEXT

Syndication_Persons_Out

HASH_ID DT_WSTR 35
PERSON_ID DT_I4
ITEM_ID DT_I4
NAME DT_WSTR 255
EMAIL DT_WSTR 255
URI DT_WSTR 255
TYPE DT_WSTR 20

Syndication_Persons_Attributes_Out

HASH_ID DT_WSTR 35
PERSON_ID DT_I4
ATTRIBUTE_ID DT_I4
ITEM_ID DT_I4
NAME DT_WSTR 255
VALUE DT_NTEXT

Syndication_Feed_Elements_Out

HASH_ID DT_WSTR 35
ELEMENT_ID DT_I4
ITEM_ID DT_I4
OUTER_NAME DT_WSTR 255
OUTER_NAME_SPACE DT_WSTR 255
VALUE DT_NTEXT

Syndication_Feed_Attributes_Out

HASH_ID DT_WSTR 35
ATTRIBUTE_ID DT_I4
ITEM_ID DT_I4
NAME DT_WSTR 255
VALUE DT_NTEXT

Then we're going to alter main.cs and create a bunch of custom methods that can send data to these various outputs. Our altered class is going to look like this:

Figure 17. Main.cs Class Diagram

Each of these methods (except for failComponent, Input0_ProcessInputRow and ProcessFeed), tie directly to an output specified. Input0_ProcessInputRow passes control to the ProcessFeed method which is responsible for calling all the other methods to output data. In the following matrix you can see the flow of data from method to output buffer to database table:

Method		Output Buffer		Database Table
ProcessSyndicationCategories	→	Syndication_Categories_Out	→	SYNDICATION_CATEGORIES
ProcessSyndicationCategoriesAttributes	→	Syndication_Categories_Attributes_Out	→	SYNDICATION_CATEGORIES_ATTRIBUTES
ProcessSyndicationCategoriesElements	→	Syndication_Categories_Elements_Out	→	SYNDICATION_CATEGORIES_ELEMENTS
ProcessSyndicationContentAttributes	→	Syndication_Content_Attributes_Out	→	SYNDICATION_CONTENT_ATTRIBUTES
ProcessSyndicationFeed	→	Syndication_Feed_Out	→	SYNDICATION_FEED
ProcessSyndicationFeedAttributes	→	Syndication_Feed_Attributes_Out	→	SYNDICATION_FEED_ATTRIBUTES
ProcessSyndicationFeedElements	→	Syndication_Feed_Elements_Out	→	SYNDICATION_FEED_ELEMENTS
ProcessSyndicationLinks	→	Syndication_Links_Out	→	SYNDICATION_LINKS
ProcessSyndicationLinksAttributes	→	Syndication_Links_Attributes_Out	→	SYNDICATION_LINKS_ATTRIBUTES
ProcessSyndicationLinksElements	→	Syndication_Links_Elements_Out	→	SYNDICATION_LINKS_ELEMENTS
ProcessSyndicationPersons	→	Syndication_Persons_Out	→	SYNDICATION_PERSONS
ProcessSyndicationPersonsAttributes	→	Syndication_Persons_Attributes_Out	→	SYNDICATION_PERSONS_ATTRIBUTES
ProcessSyndicationPersonsElements	→	Syndication_Persons_Elements_Out	→	SYNDICATION_PERSONS_ELEMENTS
ProcessSyndicationSummaryAttributes	→	Syndication_Summary_Attributes_Out	→	SYNDICATION_SUMMARY_ATTRIBUTES

(Continue to Part 2 -->)

Dennis and Jim's Data Engineering Blog

Saturday, July 6, 2013

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