Business Intelligence Geeky/Programming

Yamanalysis: Analyzing Yammer and Using PowerPivot on MySQL

I have blogged before about we use Yammer. Some interesting data can be gleaned from the usage of Yammer. One thing though is that the data and usage stats are limited in the Yammer area, but you can get all the data and take a look at things. I ran into Yamanalysis and decided to give it a try.

After getting Ruby, Rails, MySQL, curl/curb, GraphViz, IBM WordCloud and whatever else configured, I finally got it working. (FYI – MySQL 5.0 – you need to run the config wizard as administrator on Windows 7 or it just hangs at the end).

Pretty cool data and analysis from a higher level. Of course after getting everything working, I wanted to hit the data with PowerPivot. This sounds like an easy feat, but yet seemed to be a complicated task.

I first got the ODBC connector 5.1 for MySQL (Since PowerPivot doesn’t natively connect to MySQL,and 5.1 since that is the only one I could find reliably and get to work.), and set up an ODBC source. Tests fine.

In PowerPivot, I would run through the wizard and it would get architecture mismatches, and catastrophic failures, trying to test the connection. Ignoring that and moving forward, running a query would just hang on import forever. I tried different DSN’s, User/System DSNs, etc, to no avail.

What I ended up doing was firing up my local Microsoft SQL instance, and creating a linked server through a system DSN to the MySQL instance, then I could query the data fine from SQL. I opened up PowerPivot, connected to SQL local and then ran the query to MySQL and it work. What a workaround, what a hack, but at least I can hit the data in PowerPivot locally, which was my goal here.

Of course I could take what Yamanalysis is doing and dump to SQL, or do something similar in C# and dump to SQL, that might be a project for another day.

Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL 2005: SSIS – Pushing Data to MySQL using Script Component Destination

Sometimes, I just wonder why things that are useful in previous versions of things get removed. In SQL 2000, in DTS, there was an ODBC destination, you could write (insert, update, etc) to a foreign (non MSFT SQL) system easily. DB2 – no prob, MySQL – heck yea. For whatever reason, in SQL 2005 Integration Services (SSIS), that ability was removed (in SQL 2008 SSIS there is an ADO.NET Destination that can update ODBC sources, so they brought some functionality back).

I need to write to a MySQL database pulling data from a SQL Database, using SSIS 2005. What are the options? Well, the best I could come up with was a Script Component Destination in my DataFlow, this is how I did it:

1) Create a new SSIS Package, throw a DataFlow on the Control Flow, Add your connections (let’s say SourceDB_SQL – your source data, and then DestDB_MySQL – your MySQL destination, it needs to be a ADO.NET Connection, you need to install the MySQL connection – I installed this

2) In your DataFlow, create your OLEDB Source and get your query returning data, throw a Script Component on the Data Flow and make it a Destination.


3) Open the Script Component, set your input columns up, and then your Connection Manager


4) Open the actual script, and you just have to add a few lines of code:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc

Public Class ScriptMain
    Inherits UserComponent

    Dim mySQLConn As OdbcConnection
    Dim sqlCmd As OdbcCommand

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
        mySQLConn = CType(Me.Connections.MySQLDatabase.AcquireConnection(Nothing), OdbcConnection)
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        sqlCmd = New OdbcCommand("INSERT INTO steve_test(ShipTo, YearValue) VALUES(" & Row.ShipTo & ", '" & Row.YearValue & "')", mySQLConn)

    End Sub

    Public Overrides Sub ReleaseConnections()
    End Sub
End Class

Run it and you are done! Easy. Now you can write data into MySQL using SQL 2005 SSIS (or any ODBC destination if you can get it to work :))

Geeky/Programming Ramblings Random

Why isn't there a Web 2.0 Ajax Visual Studio?

Was thinking about this today. You can now write Word docs, Excel spreadsheets, Powerpoint presentations and the like all online (Google Docs, Zoho, etc, etc). You can record video straight to websites through your webcam, you can video conference directly through the web.

Visual Studio in the Cloud:

Why can’t you code directly into the web?

I would like to see an app that lets you create a new .NET project through a web interface, reference dll’s if you need to (upload them to your “space”) and then go about creating code, Intellisense through Ajax, you hit compile, it sends it off to the server, compiles, and gives you a result. You can then browse to your exe or your new website and view the results.

No need for a bulky IDE installed on your computer, no worries about dependencies, etc. You could code C#/VB.NET on linux and a mac with no need for mono (although you couldn’t run the exe’s – it would be the most beneficial for web apps)

You could target different versions of the framework, use new features if you wanted, all that. Heck why not have the same thing for your database. mySQL already has it with phpadmin and all the other tools, you can query and do whatever you need to through the web. Where are the offerings like this for MSFT products? Maybe I just don’t know about them.

There is CodeIDE – but it is limited in languages and options. I want to see more of a full fledged Visual Studio IDE in the browser. Why? Because I want to be able to fire up a computer and just go to work, no installing, no waiting, no upgrading.

I can already see it now, Adobe Air IDE’s you can run on your desktop and sync up source code to the online IDE.

One feature built right in to this “online IDE” would be source control, revision history, etc.

I might not be possible right now, but I say give it a few years, and we will see a product like this come out, and I can’t wait.

Blogging Geeky/Programming

Switching Hosting – Moving to Media Temple (MT)

A few months ago I decided to consolidate my hosting and sites to one server, and I went to Server Beach, and got my own Windows 2003 server. I needed and PHP for some things I had so it was kind of a unique situation.

Server Beach is awesome, and if I needed more control I would definitely go back, but I decided it was too money for what I was doing, so I found Media Temple (MT). They have one click WordPress install which was nice, and you can host like up to 100 sites on one account on their “grid” service, for like 20 dollars a month.

What this did leave me with though, was I needed to convert the services I had in ASP.NET to PHP, so a week or so ago I decided to make the switch and converted those services over. PHP just reminds me too much of ASP Classic, very hacky and you have the ability to just put anything anywhere. Oh well, it works now and all my sites are over there for now (except a couple that I will move when their hosting expires). I am liking MT so far and hopefully it goes well, its cheap enough and allows me to do what I need.

I do like that they have the MySQL admin available so you can manage the DB’s directly. In one of my sites, I had to update all the posts at once, and I just wrote a query to do it, saved a ton of time.

Now if I can just get all my DNS and domain registrations in one place, I will be set 🙂


Ruby on Rails and MySql .. on Windows Vista

So, this evening I got the urge to get Ruby on Rails working on Vista, with MySql. I haven’t done much with RoR, but figured I would give it a go. I have this test hosting account that has RoR hosting, so that is what got me somewhat motivated…anyway’s, on with the show.


Install Ruby

Installing Ruby is pretty easy. You can follow the tutorial pretty much step by step from the site, except it is tailored to *nix machines (Mac, Linux) as far as paths and stuff..

1) Download and install ruby..(

2) get RubyGems, run ruby setup.rb

3) Get rails:…. gem install rails –include-dependencies

Create Application

At the command prompt:

rails c:railsblog

cd railsblog

ruby script/server

test it on http://localhost:3000

if all is well, you will see a cool welcome screen..

Now.. lets actually start making our blog app by generating a controller..(remember , you need to call “ruby” before executing these scripts.. in Mac, etc you don’t have to)

ruby script/generate controller Blog

Then edit your blog_controller.rb and add some code:

class BlogController < ApplicationController
    def index
        render :text => “Hello World!”

try it (http://localhost:3000/blog).. whoops.. error?

no such file to load — sqlite3

check under your app dir (c:railsblog) your configdatabase.yml

it is set to sqllite.. we need to get MySql installed and configured

Install MySql (5.0.51a)

This is a whole nother debacle. MySql doesn’t really work right on Vista.

download MySql (, install it

you will notice.. the config assistant doesn’t run at the end..

Configure It, Hack It, Swear At It

First, in the MySql/bin directory, set both the MySql config assistant and the mysqld-nt.exe to run in XP Sp2 compatibility mode, and run as administrator (this will
allow the service to start once the config assistant is done, once we hack to run anyway’s)

In your application logs in event viewer you will see

Activation context generation failed for “C:Program FilesMySQLMySQL Server 5.0binMySQLInstanceConfig.exe”.
Error in manifest or policy file “C:Program FilesMySQLMySQL Server 5.0binMySQLInstanceConfig.exe” on line 6.
The value “asAdministrator” of attribute “level” in element “urn:schemas-microsoft-com:asm.v1^requestedPrivileges” is invalid.


I guess older version work, but 5.0.51a doesn’t. You can see in the error, “asAdminstrator”, it should be “requireAdministrator”, we need to hack the exe..

download resource hacker –

Open the MySqlInstanceConfig.exe in Reshack, ctrl+f, search for “asAdministrator”, change to “requireAdministrator” save and compile the exe over the old one..

Whoo hoo! The config assistant runs!

Basically the defaults, except I chose

“multilingual” on the character set screen,

and checked the “Include Bin Directory in Windows PATH” box on the Windows Options Screen

and – setup a root password on the security screen!

after it is all done, I like to secure my local machine, go to the my.ini in your MySql directory, and under the [mysqld] add


so only local apps can connect..

Test MySql by opening a cmd prompt,

mysql -h localhost -u root -p

hit enter, it will ask for a password , and you should be able to login

Configure MySql For Our App

login to MySql using the cmd above..then

CREATE USER ‘blog’@’localhost’ IDENTIFIED BY ‘blog’;


Now we have a database called “blog” a user called “blog” with password “blog”

We need to tie Ruby to MySql now…


gem install mysql

now, you probably have your mysql directory in your path, but since you need to restart your machine or explorer for new paths to show up, it doesn’t work yet..
so we need to make sure we do that, otherwise, if you try hitting http://localhost:3000/blog you will get a libmysql.dll error popup. I just killed explorer.exe and
start->run explorer in task manager, and then hit http://localhost:3000/blog again and saw the “hello world”

Yesssss… it works. Now , for the fun stuff, actually coding and creating tables, and more!!!!


Server Move: Hosting My Own Site(s)

Well, I have been on HostMySite for a few years now, and I just wasn’t liking it. I didn’t have control, and couldn’t do everything I wanted to. Last week I signed up with ServerBeach and I have my own dedicated server. I am running Windows 2003, IIS, SQL, MySQL, PHP, etc.

I changed for now my DNS to GoDaddy from Active-Domain, since GoDaddy has SPF records (for Google talk federation, etc). I might change that up here in the future as well.

Transferring a domain takes way to long! Took like 4 days overall. Also, I first went with EasyCGI , a VPS solution, but the box couldn’t get to the Internet! I put in tickets, etc, they just wouldn’t fix it, so I canceled and went to ServerBeach. I am liking them so far, they had the server provisioned in about 4 hours.

One thing I can do now as well, is write a site for myself and import all the log files (Firewall, SMTP, All the Sites, FTP, etc) and have a nice little reporting solution.

ServerBeach is good, very professional. If you want to sign up, use referrer code BW27Q37B6D  (

To get PHP/MySQL/WordPress, I loosely followed these three tutorials

How to Install PHP on IIS 6.0
How to Install MySQL On IIS 6.0
How to Install WordPress on IIS 6.0

For my ASP.NET Site, it was just copying over files and configuring IIS a little. For my blog, I used this dbbackup plugin ( and then once I had my WordPress files over, and MySQL/PHP running, I connected to the instance of a DB I created and ran the SQL backup script, which created everything the way I needed it for WordPress.

For Mail, I use the built in SMTP in Windows 2003. I just had to turn on relaying, but yeah, spammers, so I just allowed the local server to send mail, and it works. Tested it using this ( and it worked.

Other than that, its pretty much basic server admin stuff. Firewall, FTP, etc. Everything is running smooth… for now 🙂

I will just have to make sure I have backups!