THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Migration from DasBlog to CS Complete

As you can see from the multitude of archived posts,I have managed to migrate DasBlog posts (stored in XML files) to the CS 2.0 SQL Server data model. Let me outline the migration process.

  1. Import the DasBlog XML files. I used SQL Server 2005 Integration Services to do this work.
    1. The package uses a For Each Loop container to iterate through the XML files (using the filespec ????-??-??.dayentry.xml).
    2. In that loop container, I placed a Data Flow Task which contains an XML Source, a Data Conversion, and a SQL Server Destination. At first, the import failed, but a little tweaking of the XML Source task using the Advanced Editor to set the source content to a data type of Unicode text stream [DT_NTEXT].
    3. The final imported table had the following definition:

      CREATE TABLE dbo.DasBlogEntries(
            Content nvarchar(max) NULL,
            Created datetime NULL,
            Modified datetime NULL,
            EntryId nvarchar(255) NULL,
            Description nvarchar(255) NULL,
            Title nvarchar(255) NULL,
            Categories nvarchar(255) NULL,
            IsPublic bit NULL,
            ShowOnFrontPage bit NULL,
            Crossposts nvarchar(255) NULL,
            Entries_Id numeric(20, 0) NULL)

  2. Clean the data. The next step was to create CS compatible data from the imported DasBlog data. I wrote a query that
    SELECT 
     identity(int, 10, 1) AS PostID, 0 AS ThreadID, 0 AS ParentID, 
     'Peter DeBetta' as PostAuthor, 
     2102 AS UserID, 5 AS SectionID, 1 as PostLevel, 
     1 AS [SortOrder], Title AS [Subject], Created AS [PostDate], 1 AS [IsApproved], 
     0 AS [IsLocked], 1 AS [IsIndexed], 1 AS [TotalViews]
    ,
     Content AS [Body], Content AS [FormattedBody], '69.15.196.116' AS [IPAddress], 
     1 AS [PostType], 0 AS [EmoticonID],
     
    'enableAllOwnerNotification:S:0:5:EnableRatings:S:5:4:EnableTrackBacks:S:9:4:
    enableCrossPosting:S:13:5:CommentModerationType:S:18:4:feedbackNotificationType:S:22:1:
    SpamScore:S:23:1:EverPublished:S:24:4:'
    -- These 3 previous lines should all be one in the final code.
    -- The are broken into 3 for nicer web display

       AS
    [PropertyNames]
    ,
     'FalseTrueTrueFalseNone00True' AS [PropertyValues], 1000 AS [SettingsID],
     0 AS [AggViews], 
     3 AS [PostConfiguration], NULL AS [PostName]
    ,
     GetDate() AS [UserTime], 1 AS [ApplicationPostType], 0 AS [Points], 
     0 AS [RatingSum], 0 AS [TotalRatings], GetDate() AS [PointsUpdated]
    INTO TempPost
    FROM dasblogimport..DasBlogEntries
  3. Import the data into the CS database. I simply copied the data (the TempPost table)from my temporary database to the actual CS database.
  4. Post the posts. Using a cursor, step through each post in the TempPost table and execute the cs_Post_CreateUpdate stored procedure for each post. The code for this final step is a bit lengthier, so I will attach the script instead of include it here. Keep in mind that I have some commented code and some oddball code that was for my own use for testing and to exclude a couple of posts.
    NOTE: I have been unable to attach the script and will post it as soon as I figure out what is going on...

 

Published Monday, June 26, 2006 1:27 PM by Peter W. DeBetta

Attachment(s): step 4 - post the posts.sql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Carlos said:

Peter,

Great write up.

Can you post the package you created in Step 1 of your migration process?  I am undertaking the same task but using BlogML.  BlogML is a great tool but not when trying to import files larger than 2MBs in size.  I came across your article and would appreciate the help with  the package step.  

Thanks in advance.

Carlos

April 4, 2008 2:32 PM

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement