Donnerstag, 2. Juni 2011

Tutorial 4: How to import RSS feeds into MS SQL 2008 (Part 4)

Part 1 | Part 2 | Part 3 | Part 4

The foregoing parts explained how to create the SSIS package to import 
RSS feeds. The 4th and last part of this series covers how to setup the database
tables and stored procedures, which are called be the import package.


The headline table

The table 'fan.TutorialHeadline' is the destination of the RSS feed import. See
it's definition below.

-------------------------------------
-- final import destination
-------------------------------------

CREATE TABLE fan.TutorialHeadline
(
     
id int PRIMARY KEY IDENTITY (0,1),
     
publisher varchar(50),
     title
varchar(255NOT NULL,
     
content varchar(MAX) NULL,
     import_date datetime  DEFAULT GETDATE(),
     
linkurl varchar(255NOT NULL,
     merge_hash
binary(16NULL
ON [PRIMARY]
GO

CREATE
NONCLUSTERED INDEX [Ix_merge_hash] ON [fan].[TutorialHeadline]
(
     
[merge_hash] ASC
)
GO


The import buffer table

To avoid duplicate entries we do not update the headline table directly. We
import the RSS feed data into another import table and merge just the missing
rows into the headline table. In this tutorial the definition of both the
headline and the import tables are identical. See the definition of the import
table below.

-------------------------------------
-- import buffer table
-------------------------------------

CREATE TABLE fan.TutorialImport
(
    
id int PRIMARY KEY IDENTITY (0,1),
    publisher varchar(50),
    title 
varchar(255NOT NULL,
    content varchar(MAXNULL,
    import_date datetime DEFAULT GETDATE(),
    linkurl varchar(255NOT NULL,
    merge_hash binary(16NULL
ON [PRIMARY]
GO

CREATE
NONCLUSTERED INDEX [Ix_merge_hash] ON [fan].[TutorialImport]
(
     
[merge_hash] ASC
)
GO


The hashing function

You may have noticed the indexed column merge_hash in both tables. Hence RSS
doesn't provide a unique ID to identify a RSS data item, I create a unique
hashcode, which is calculated from the feed title and content using MD5. The
function is used by the stored procedure which imports the data. See the
function definition below.

-----------------------------------
--- hash function
-----------------------------------


CREATE
FUNCTION fan.TutorialHash
(@title varchar(100), @content varchar(MAX))
RETURNS binary(16)
AS
BEGIN
    -- create merge hash
    
DECLARE @hash binary(16);
    DECLARE @tc8000 varchar(8000);    SET @tc8000 CONVERT(varchar(8000), cast(@title as varchar(MAX)) ' ' + @content);
    SET @hash CAST(HASHBYTES('MD5',@tc8000AS binary(16));
    RETURN @hash
    END
GO


The import procedure

The stored procedure 'fan.TutorialAddHeadline' is called by SSIS package
created in the previous parts of this tutorial. The procedure is executed for
each item in a RSS feed. It creates the unique hashcode and creates a new row in
the import buffer table.

------------------------------------------
-- TutorialAddHeadline procedure
------------------------------------------

CREATE
PROCEDURE fan.TutorialAddHeadline
@title
varchar(100), @content varchar(MAX), @publisher varchar(50), @linkurl varchar(255)
AS
BEGIN
    -- create merge hash
    
DECLARE @hash binary(16);
    SET @hash fan.TutorialHash(@title@content);

    -- create headline    INSERT INTO fan.TutorialImport (publishertitlecontentlinkurlmerge_hash)
    VALUES (@publisher@title@content@linkurl@hash);
END
GO


Merge recently imported and existing data

The stored procedure 'fan.TutorialMerge' copies all rows from the import
buffer table to the destination table. It uses the MERGE command, which is new
in MS SQL 2008. The MERGE command uses the calculated hash code to find missing
rows in the destination table and inserts only new rows. After the merge the
import buffer table is cleared.
This procedure has to be called by SSIS package after the import. The next
step describes how to add this last task to the SSIS package. See the merge
procedure code listing below.


---------------------------------------
-- merge imported and existing data
---------------------------------------


CREATE
PROCEDURE fan.TutorialMerge
AS
BEGIN
    MERGE 
fan.TutorialHeadline USING fan.TutorialImport m
    ON h.merge_hash m.merge_hash
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (publishertitlecontentimport_datelinkurlmerge_hash)
          VALUES (m.publisherm.titlem.contentm.import_datem.linkurlm.merge_hash);
    DELETE FROM fan.TutorialImport;
END
GO


Adding the merge procedure to the SSIS package

The merge procedure 'fan.TutorialMerge' has to be executed after the import
to update the RSS data. Follow the steps below to add it to the SSIS package.
1. Add a new 'Execute SQL' taks to the SSIS package
2. Click on the foreach loop container and wait for the arrow to appear. Then
drag it on the 'Execute SQL' task
3. Right click the 'Execute SQL' task and choose edit
4. Make sure the task connects to your database and then edit the attribute
SQLStatement and enter the following code. Replace 'CelebrityDB' by your
database name.

USE CelebrityDB -- replace db name
GO

EXEC fan.TutorialMerge


5. Click OK to close the editor

The SSIS package is now complete. You can now deploy it on the server. When
you execute the package,  it should look like the one on the image below.

Executed SSIS package:


Final Result

After the import the RSS feed data is stored in the table 'fan.TutorialHeadline'.
You can read the RSS feeds now with your SQL Server Management Studio.

Result of the RSS import:




Thank you for reading this tutorial!

More tutorials are coming soon. Your feedback helps me a lot to improve this
blog. Please leave a comment!

31 Kommentare:

  1. Awesome Tutorial, very helpful, has given me my first taste of programming for years! XML, RSS, SQL, Stored procedures and fuctions, hash generator, this tutorial has it all! Only point for any other newbies, fan.xxx should be dbo.xxx in english!

    AntwortenLöschen
  2. Thanks for the great tutorials. Download feed is working fine, but i am not able to pull data from XML to Table using Import RSS File Script Task. Its not giving any error.

    Any help ?

    AntwortenLöschen
  3. I think they changed the format of the RSS feed.
    If you read the XML file, there are no references to ""
    I got SOMETHING into the [TutorialHeadline] table by doing the following
    Look for the line which reads :
    XmlNodeList itemList = doc.GetElementsByTagName("item");

    Change it to :
    XmlNodeList itemList = doc.GetElementsByTagName("entry");

    this does not fix the issue in its entirety, but it does get it to do something.

    AntwortenLöschen
  4. After reading this post its seems very easy that how to import RSS feed into MS SQL. Thanks for sharing this with us, Carry on the great work.

    Regards,
    Healthcare Websites

    AntwortenLöschen
  5. Tevida aggressive buyer. First, make a small review. Review some customer comments and comments on specific herbal pills before buying them. Always be careful with your safety, since you do not want to
    https://www.supplementsforfitness.com/tevida/

    AntwortenLöschen
  6. Keto 180 Shark Tank defines personal characteristics of health merchandise.Keto 180 is a 100% pure weight loss product which Ddminishing stored fats and calories from the bodies

    AntwortenLöschen
  7. Wellness Trials is a Health & Wellness Product We update our users with health tips and health products reviews. If you want to know any information about health o& Wellness Product (Side Effects & Benefits) Feel Free To ask Wellness Trials.

    http://www.wellnesstrials.com/keto-pure-diet/

    https://www.pinterest.com/claimwellnesstrials/

    https://www.instagram.com/claimwellnesstrials/

    http://claimwellnesstrials.over-blog.com/

    https://works.bepress.com/wellness-trials2/

    AntwortenLöschen
  8. Dieta Salutares is an best site where you can get detail about health, wellness and beauty products that really helps you to select best products according to your needs. This site also provides you latest supplements that is more helpful to make your life better.

    http://dietasalutares.it/

    https://www.facebook.com/Dieta-Salutares-582647302249651/

    AntwortenLöschen





  9. Pinterest

    Instagram

    Twitter/

    If you are searching for male enhancement then you came right place Vandafil is help to Male Enhancement and increase testosterone level in Man Body.just click on given link above

    Vandafil Advanced

    https://www.supplementmegamart.org/vandafil-advanced/





    AntwortenLöschen


  10. Pinterest

    Youtube

    Twitter


    Zephrofel By Nutrifit web We have best and natural Product of health & wellness supplement you can get several benefits with Us.Different health products including skincare,weightloss,muscle and male enhancement.
    Official Site: https://www.nutrifitweb.com/


    https://www.nutrifitweb.com/zephrofel/

    Overblog

    work-bepress




    AntwortenLöschen

  11. Our Supplements Angles is that people find best quality health products they need; that the health products are safe and affodable for them. Supplements Angles (Supplements Angles)

    AntwortenLöschen
  12. Are you looking for a natural and effective way to enlarge your penis? You may have tried several techniques, and all your efforts might have gone in vain. Let me give you an introduction to the Penis Enlargement Bible review

    AntwortenLöschen
  13. Abundance Manifestor download is an online program aimed at those who want to change their lives. It is one of the latest guides that revolves around the law of attraction.

    AntwortenLöschen

  14. Healthy GNC >>>> Healthy Gnc is one only Health & Wellness supplement hub in usa. We have natural ingredients supplements with no side effects. we are providing Muscle building , Weight Management , weight loss and wellness supplements.

    For More Information Click Here >>>> http://www.healthygnc.com/


    AntwortenLöschen
  15. Dragons Den Diet is leading company in United Kingdom which sells various health related products.it has a vast range of products like weight loss supplement,male enhancement,muscle related products & skin care.we are offering the best discount in the market and selling best quality products.

    For More Information Visit Our Official Website >>> https://www.dragonsdendiet.com/

    https://www.dragonsdendiet.com/nitro-strength/

    AntwortenLöschen
  16. Ultra Fast Keto Boost is a natural weight loss supplement that targets the fat from the root level and provides you a healthy and fit body. With the help of this product, you are going to get the permanent result and desired result.

    https://twitter.com/martsupplements/status/1169152488488017920
    https://www.saatchiart.com/art/Painting-Ultra-Fast-Keto-Boost-Shark-Tank-Reviews-Cost-Weight-Loss-Pills/1320697/6575437/view
    http://www.apsense.com/article/ultra-fast-keto-boost-shark-tank-reviews-cost-weight-loss-pills.html
    https://www.pinterest.com/pin/855754366678769778
    https://www.youtube.com/watch?v=Wtwl19PZuKs&feature=youtu.be
    http://fliphtml5.com/elqsi/hfsi/
    https://supplementsmegamart.hatenablog.com/entry/Ultra_Fast_Keto_Boost
    https://supplementsmegamart.blogspot.com/2019/09/is-ultra-fast-keto-boost-scam-diet.html

    AntwortenLöschen
  17. Web Bitcoin Club is an innovative payment network and a new kind of money. Find all you need to know and get started with Web Bitcoin Club on https://webbitcoinsclub.blogspot.com/. We use cookies to ensure you get the best experience on our website.

    AntwortenLöschen
  18. The male enhancement supplement Alpha Titan Testo contains specially chosen ingredients, which together could potentially improve your performance in the bedroom and boost your libido. This product is intended to assist you in your efforts to reclaim your youthful exuberance and, most importantly, make it easier for you to fulfill your partner with a pleasurable and intense sex life.>>> https://www.supplementsmegamart.com/alpha-titan-testo/

    https://bit.ly/2ogd8XT
    https://sites.google.com/site/alphatitantestogain/
    https://myhealthykart.blogspot.com/2019/10/alpha-titan-testo.html
    https://buyalphatitantesto-32.webself.net/
    http://buyalphatitantesto.mystrikingly.com/
    http://alphatitantesto.simplesite.com/
    http://buyalphatitantestopill.bravesites.com/
    https://buyalphatitantesto.yolasite.com/
    http://buyalphatitantesto.jigsy.com/

    AntwortenLöschen
  19. Read this article that shows the features of Okinawa flat belly tonic and its complete details. We shall compare Okinawa fat belly tonic reviews to get better knowledge about the product.

    AntwortenLöschen
  20. If you know the courses that help you make money, Kibo Code is one of them. I mean the Kibo code is a very profitable course for e-commerce companies. It is unique and highly predictable due to the business strategies presented in the course. Read this Kibo Code Review carefully so that you can make an easy decision of whether to register in the Kibo Code System or not

    AntwortenLöschen
  21. Alpha Titan Testo :-When a product uses the best quality ingredients, only then can you be sure that the results will also be the best? All the other supplements out there use old techniques and copied recipe while we have made something original, and use only the best. Alpha Titan Testo Reviews canada has used many ingredients, but we would like to tell you about a few of them because they do a lot more than others.

    Alpha Titan Testo Reviews
    Facebook

    AntwortenLöschen
  22. I had always found that if I actually made less Testotserone that I would get additional Testotserone. Alpha Titan Testo Reviews is match for a king. The sole sensible part was Testotserone. Alpha Titan Testo That was my favorite part. This is often the scoop if this was out of control. It's the deed. Let's start with what Testotserone is, as a result of a heap of voters really do not comprehend that.

    https://www.topbodyproducts.com/alpha-titan-testo/

    https://www.topbodyproducts.com/

    AntwortenLöschen
  23. Hello Friends My Name is Elijah Pellinger I am From United States I Want To Introduce You about My Website Many Health Related Products Are Available on our Website.Those Products are Beneficial for your Health You can Live your Lifestyle Healthier.

    https://fitness-trends.life/

    AntwortenLöschen

  24. I realize how bustling everybody is today.

    It requires quick change. Great?what's next? In a new overview, the greater part of all North Americans concurred. Take that to

    mind: It was a decent an ideal opportunity to bring that thing up. Everything begins without having a Wellness that guts a capability for a

    Wellbeing. Indeed, similar to wizards say, "You win a decent a considerable lot of you lose a few." Wellness totally functions admirably if everything is finished

    right. I suggest that you drench yourself inside Wellness. All things considered, "I would prefer to have a container before me, than a

    frontal lobotomy."



    https://www.nutrahealthpro.com

    https://www.facebook.com/nutrahealthpro

    https://twitter.com/nutrahealthpro

    https://www.instagram.com/nutrahealthpro/

    AntwortenLöschen
  25. This all assumes a job when a Wellness that crushes a similarity for a solid life. This was a major bad dream. I may have to help the same number of experts as I can. It is essential for the new joint agony year. No matter what, I'm acceptable with constant torment. This post will furnish you with aides and tips for body torment. I will compose a manual for help answer those Healthy inquiries.
    https://www.nutrahealthpro.com

    https://www.facebook.com/nutrahealthpro

    https://twitter.com/nutrahealthpro

    https://www.instagram.com/nutrahealthpro/

    AntwortenLöschen

  26. Hold their eyeballs glued here to examine greater. That was an extraordinary opportunity. There are several details concerned. You do not even want to succeed at Male Enhancement. this is a way to spend a few greenbacks on serving up this. this is correct in normal activities as properly.

    Read More - https://www.nutrahealthpro.com/pure-vigor-x/

    https://nutrahealthpro1.blogspot.com/2021/01/purevigorx.html

    https://sites.google.com/view/pure-vigor-x-review/home

    https://www.facebook.com/nutrahealthpro/posts/211865677319900

    https://twitter.com/nutrahealthpro/status/1354741854311378946

    https://in.pinterest.com/pin/596867756863817361

    https://www.instagram.com/p/CKlgxLdlJaB/

    AntwortenLöschen

  27. It makes me can't help thinking about why I like the blemishes. The squeaking sound that the refrigerator makes resembles an ensemble to me. I rub my fingers along the flawed crease and it really quiets me.
    https://www.nutrahealthpro.com/qualia-mind/

    https://www.instagram.com/p/CLwZ0ybl97Q/

    https://www.facebook.com/nutrahealthpro/posts/232229241950210

    https://twitter.com/nutrahealthpro/status/1365311986087694341

    https://www.pinterest.com/pin/596867756864985026

    https://sites.google.com/view/qualiamind-reviews/home

    https://nutrahealthprodiet.blogspot.com/2021/02/qualia-mind.html

    https://www.smore.com/9t7vw/edit

    AntwortenLöschen