Source Code for .Text to Community Server DTS Package Engine

[Preface]  This is the original DTS package engine code for my DotTextCS utility; the DTS engine in CSverter is based on this code, but has dependencies on some of the Community Server business objects and has been refactored to reduce total LOC as well as performance enhancements (I will post the revised engine code once Robert and I get it stable).  It’s basically just raw code to dynamically generate (n) number of DTS packages for corresponding .Text BlogID’s and should be considered a stepping stone for writing a DTS based .Text to Community Server migration tool (as Robert McLaws has so elegantly done)…it’s nothing more than a table/column mapping utility from the .Text db to the CS db.  Also, all primary keys are kept intact between the databases, so any corresponding references remain unbroken. 

I originally set out to write a single DTS package to migrate .Text to Community Server, but after some prodding from the community I decided to revise the package(s) to be dynamically generated from managed code, and to support migration of (n) number of blogs based on a .Text BlogID (from the .Text blog_Config table).  It is worth mentioning that this code only migrates blog content itself, not the corresponding user info/blog info/etc; as such it should be viewed as a reference for table/column mappings between the two products and not as a full featured migration tool.  Basically, this utility picks up after all users/blogs have been created by some means, and migrates all associated blog content over based on that criteria.  So, onwards.  [/Preface]

As is the case with most projects, writing the code was not the hardest part…in this case it was figuring out the table/column mappings between the .Text database and the CS database…fortunately the folks over at Telligent kept the schemas (for the blog portion at least) somewhat similar, but it was still very much a hunt and peck ordeal.  After figuring all that out, the rest was quite easy; I saved the DTS package as a (classic) VB .bas file, then ran the VB.Net conversion wizard.  I would very much have preferred to write this in C#, but as DTS exports to VB natively, it was a no brainer.

The design of the engine itself is quite simple; the requirements (for me at least) were to support the following:

• Both SQL authentication and Windows authentication to SQL Server.
• Support for disperate computers (meaning db’s residing on different machines).
• Dynamic generation of DTS packages based on the .Text BlogID (and the associated email address for said BlogID, everything is keyed off of this value for CS).

There are 3 classes involved with this:

• A User class which holds all of the user based data (.Text user info).
• A Config class which holds all configuration data (anything related to SQL Server db’s (source, destination); logins, etc).
• A Package class which dynamically generates all of the DTS packages based on data in the above classes.

What this code does:

• Migrates all blog content (posts, comments, trackbacks, categories, etc) over to CS.
• Migrates all links/link categories over to CS.
• Migrates all URL’s/referrals over to CS.
• Maintains all primary key values from .Text over to CS.

What this code doesn’t do:

• Migrate over the user info itself…this will still need to be set up by some other means (and Robert’s CSverter does a great job of this)…this tool migrates blog content only.
• Change any links you’ve made back to your own blog into your new CS URL (though this is trivial to accomplish via SQL statements).
• Update your page/aggregate hit values in CS (again, trivial via SQL statements).
• Contain very many comments…it’s simple in what it does so they really aren’t necessary.

If you modify the source code/choose to wrap your own GUI around it/whatever, please notify me.  I will be releasing another source code update as soon as Robert and myself finish up CSverter (the Engine concept will stay the same, but there will have been quite a bit of refactoring done).  In order to get a successful build, you must add a reference to the Microsoft DTSPackageObject library (available on the COM tab), and you should have at least service pack 2 applied to the SQL Server Client Tools (v2.0.x of dtspkg.dll).  This is also a good project to look at if you need to brush up on dynamically generating DTS packages from code.

Have a look at the code here.