Community Server Migration Script -- Round 2

[Update 02/11]  Please see my new post for more details concerning this utility, sorry for all the ping-ponging of posts concerning the migration tool.  The end result will be worth it.  [ /update]

Ok I’ll admit it, my previous post about migrating from .Text to Community Server was a bit arduous/tedious/hacky, so I’ve taken the time to write a proper managed console application that (attempts) to take the some of the pains out of the migration, namely not having to manually update each of the 9 scripts in the Transform Data Tasks (by auto-generating the DTS package for you...so yes, it still uses DTS, I wouldn't have it any other way).  You can download the console application here.  Before running the application, make sure you properly configure the DotTextCS.exe.config file; I’ve commented each key with a brief description, though it should be quite straightforward.  In a future release (soon), I’ll try to get rid of having to manually find your BlogID, UserID PostAuthor, SectionID, and SettingsID…but for now that remains the only manual process.  Once again, to get those values you will need to do the following:

• For the BlogID setting, look in the blog_Content table in your .Text database.
• For the other settings, they can all be gathered by looking at the cs_posts table in your Community Server database.

You should start with a fresh install of the Community Server database, then set up your blog(s) and make a test comment (so that the UserID (etc) information is logged to the cs_posts table for harvesting).  After configuring the .config file, run the console application, simply hit ‘y’ to indicate you want to create the package, and then run the package from Enterprise Manager (or you can execute the package directly from the console app, there is a key called ExecutePackage in the .config file…setting this to true sidesteps having to go to Enterprise Manager, though you might get an error or two…they can be ignored).  You will also need to execute the following SQL statements to tidy everything up, I’ll just quote my previous post:

If you have linked back to your own blog, execute the following SQL statement in Query Analyzer to change all the links to your new URL (using your URL’s of course, I’ve left mine in for reference):

DECLARE @originalURL varchar(100)
DECLARE @newURL varchar(100)
SET @originalURL = 'http://jaysonknight.com/blog'
SET @newURL = 'http://jaysonknight.com/cs/blogs/jayblog'
UPDATE    cs_Posts
SET       Body = REPLACE(CAST(Body AS nvarchar(4000)), @originalURL, @newURL),
       FormattedBody = REPLACE(CAST(FormattedBody AS nvarchar(4000)), @originalURL, @newURL)

If you have posts over 4000 chars long, you should be writing novels for a living instead of doing this.  If anyone knows of a work-around for the 4000 char limit, please share.

If you would like to update your hit statistics, use the following SQL statement (replace the db names with the .Text db you’re migrating from):

UPDATE    cs_Posts
SET       TotalViews = [your_db]..blog_EntryViewCount.WebCount
FROM    [your_db]..blog_EntryViewCount
WHERE    cs_Posts.PostID = [your_db]..blog_EntryViewCount.EntryID

UPDATE    cs_Posts
SET       AggViews = [your_db]..blog_EntryViewCount.AggCount
FROM    [your_db]..blog_EntryViewCount
WHERE    cs_Posts.PostID = [your_db]..blog_EntryViewCount.EntryID

I’ll get these dumped in the utility in a future release.  The same previous caveats apply, namely links/post categories don’t sort properly…also, your post categories will also show up as link categories, just delete them using the CS admin page…I haven’t figured out the column mapping for those yet.  I have tested this application on numerous (fresh) Community Server databases and am batting 100%, but let me know how it works out.  If you want the source code, ping me and I’ll happily provide it, and I’ll post it to my site once I get it cleaned up (and when it has all the functionality I want).  This is a huge improvement over my previous solution IMO…but maybe that’s just me.  Good luck, and let me know how it goes.