Using PowerShell to Archive Twitter Feeds–Preserving the wisdom of Paul Randal

If you didn’t make it out to the PASS Summit then you missed out on a lot of great content.  If you’re not on twitter and following some of SQL Server’s best and or rowdiest than you’re missing a lot.  And if you didn’t follow Paul Randal while he was tweeting from the session he and Kimberly L. Tripp presented then you missed a great refersher course on SQL fundamentals that are critical to your success in this field.

The challenge of dealing with great content burried in tweets is figuring out how to capture that information in a way you can reference it later. I took a queue from of Bruce Payette’s book Windows PowerShell in Action and decided I’d try tackling this problem. 

I started by visiting the PaulRandal twitter page. If you just wanted to download his last 20 tweets you could just use the RSS feed: http://twitter.com/statuses/user_timeline/36394357.rss .  How do you get the others? I got around the 20 tweet limitation by  favorite-ing Paul’s tweets starting at the beginning of the conference and then working my way toward the current date:

Every time I selected 20 of Paul’s tweets as a favorite I just ran this PowerShell script and appended it to a file that I was storing them in. 

 ([xml] (new-object net.webclient).DownloadString(“http://twitter.com/favorites/76699854.rss”)).rss.channel.item | format–table -autosize description, pubDate, link >> PaulRandal.txt

After that I used SSIS to load them in a SQL db and help me clear out a few of the tweets that would be of little use without the other half of the conversation and what not. 

I did learn a couple of interesting PowerShell quirks during this process.  The first is that your output is truncated based on your available screen size.  I began writing this script while running the PowerShell command in a 1280 x 1024 screen.  When my output was restricted I took a chance and tried running it on my laptop where the resolution is ste to 1920 x 1200. Running the download at a higher resolution was good enough to solve problem. (I haven’t yet found out why PowerShell kept trying to truncate in the first place. If you have ideas on this feel free to use the comments.)  The second was seeing the real differenece between the “>>”  and “>” operators.  By using a double “>>” instead of a single “>” I was able to append my output instead of completely replace it every time I ran my script.  Next time I’ll figure out how to pump it straight into a table so that I can skip the SSIS step.

Finally here’s what I ended up with:

And here’s the whole list in a more readable format:
https://sqlvariant.com/BlogSupport/Images/TwitterRSSFavorites/PaulRandalPASS2009blog.xlsx