[Update 02/08] Please view my latest post about migrating from .Text to Community server, or if you like to do stuff the hard way, by all means continue reading this post. [ /update]
If you have never heard of DTS, ActiveX DTS Scripting, or VBScript (or have no interest in migrating from .Text to Community Server RC2), run away.
Please bear in mind that this is a script I wrote for myself, but the community has shown some interest so I’ve chosen to share it. I would HIGHLY recommend testing it on a fresh CS DB install. This is a bit of a manual process, so I'll do my best to list out the steps here. And this will entail some knowledge of DTS and ActiveX DTS scripting (in vbscript) of which neither am I an expert ;) Also, this only works on RC2 (to my knowledge at least…I haven’t tested it on other releases, nor do I plan to), though if you discover it works on other versions please let me know.
The DTS package can be found here. Load the package up in Enterprise Manager (from the DTS folder, right click and choose Open Package, browse to the unzipped .dts file...for what it's worth I did this in Sql2k). Before doing anything, you will need to know your UserID, PostAuthor, SectionID, and SettingsID in the community server db, you can find this information in the cs_posts table. If it's a fresh db install (which again I recommend), just do a test post to the blog you want to migrate to then have a look at the cs_posts table for this info. Write this stuff down. Now here's where the fun begins :)
In the DTS designer, change the DotText connection to your existing .Text db, and the same with the CommunityServer connection. You will be prompted to delete existing DTS tasks, just hit ok (make sure none of them are selected). For each Transform Data Tasks task, you will need to go to the properties dialog, then click on the Destination tab, at which point you will get a dialog stating that the column reference doesn't exist (b/c we changed connections earlier)...this is fine, just note the column it's specifying, hit ok, then select that column from the dropdown. Click on the transformations tab, click edit, click properties, (this is the most tedious part, bear with me) and in each code block, look for references to DTSDestination("UserID", or "PostAuthor", or "SectionID", or "SettingsID"), and change those values to the ones you noted earlier. If the "UserID" is 1001, don't change it (only in the blog_Content to cs_posts DTS task...which is also the longest script, the other 8 are fairly short), anything you didn’t post to your blog falls under that UserID (anonymous). Save the package often. Read that line again. Once you've finished with this, right click in the designer and go to package properties and verify that the value for "Limit the number of tasks executed..." is set to 1 (it should be, but check anyway, if it tries to execute more than one task at a time you'll get referential integrity errors and you'll have to start over). If you're super paranoid, just execute each task individually starting with the one furthest to the right. If you're not paranoid, execute the entire package and hold your breath. If you do get errors, just note the task that generated it, fix the error, and re-execute that task (again, if there is more than one error, work your way from right to left). If everything goes off without a hitch, there are still 2 more steps to get everything tidied up.
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
For some reason, this doesn’t update the web hits in the blog admin page…even though it should. I’ll keep hunting around for the column used for that, but I’m pretty sure it’s cs_Posts.TotalViews. Regardless, your migration should now be (relatively) complete. If it doesn’t look right, try executing the following stored procedure in the Community Server db:
cs_weblog_UpdateWeblogRecentContent_Job, [the settingsID you noted from above]
From what I can tell, these steps move everything over correctly (actually, there is one issue; the blogroll/category links don’t sort correctly, you have to do this manually; there is a SortOrder column in the cs_Links table which needs to have ascending values depending on how you want your links sorted…it doesn’t take long to do this and works correctly when adding new links, or you can reorder your links from the admin page). All posts, comments, trackbacks…everything is recategorized, etc. If you notice anything that doesn’t work, notify me and I’ll fix it. Also, if you modify anything or find a way to make this more automated, please let me know (and I’ll continue to work on it as well). One more request (seeing as I put a bit of time and effort into this), if you recommend to anyone or find it useful, etc…please link back to this post. My end result is here. Good luck, and I look forward to hearing about success/failure/gripes/etc.
Posted
Mon, Feb 7 2005 5:37 PM
by
Jayson Knight