MongoDB and SSIS

Below is a very simple proof of concept that I’ve been batting around in my head awhile.
There is much talk of the validity of MongoDB (http://mongodb.com) and even of it’s replacing SQL Server. I am in no way advocating this. MongoDB is great for some applications, but not a full ACID compliant database in my view.

However, if you are a webdev and need a quick and dirty database and you don’t like to mess with Access or a full fledged RDBMS MongoDB is perfect.

My goal with this was to bring these tools together and utilize the strengths of both applications. MongoDB for it’s lightning-fast execution, and SQL Server for it’s analytics and storage on the back-end.

So here we go:

1. Open Visual Studio and Select New BI Integration Services Project

2. Add a DataFlow Task

3. Add a Script Component to the DataFlow as Source.

4. Add an Excel Destination for our purposes.

5. Right-click Script Component and go to Edit

6. Select “Input and Outputs”. I rename mine from Output 0 to “myOutput” in this step for clarity.

7. Select Output Columns. Click “Add Column”. This will add the columns from Mongo that you want to export to Excel.

8. I added name and ip selecting Unicode string as type for both.

9. Click on script and the “Edit Script” button.

10. With script window open I right-click References and add the MongoDB.dll (available here: http://github.com/downloads/samus/mongodb-csharp/MongoDBDriver-Release-0.90.0-Beta-1.zip). I apply to all and say yes.

11. Add “using MongoDB” to the top of the script.

12. I used the following script to connect and extract the data from the MongoDB. Then built the script (don’t forget to do this before you close the script window).

Mongo myMongo = new Mongo();
        myMongo.Connect();
        var db = myMongo.GetDatabase("citizenInput");
        ICursor<Document> cursor = db.GetCollection<Document>("submissions").FindAll();
        foreach (Document document in cursor.Documents)
        {

            myOutputBuffer.AddRow();
            myOutputBuffer.name = document["name"] == null ? "" : document["name"].ToString();
            myOutputBuffer.ip = document["ip"] == null ? "" : document["ip"].ToString();

        }

13. Drag the green arrow to connect to the excel destination.

14. Right-click excel destination and select “Edit”.

15. Click the new button to add an OLE DB connection manager.

16. Fill in the destination file path. First row will have column names. Press ok.

17. Click new for name of excel sheet. Select OK to have it built from the outputs. Pick Excel_Destination as the name.

18. Click mappings to double-check mapping is correct.

19. Click the play button to run the package. Mongo must be running on the same machine in this configuration (and have data of course).

20. Viola! Data in excel.

While this is a very basic example of utilizing SSIS with Mongo you could go much further in depth.

Other ideas might be creating a nightly task to pull the Mongo data down with SSIS to a SQL Database, then clear the Mongo database (all with SSIS). Another possibility is to go the other way and populate a Mongo database with SQL Server data on the fly.

In conclusion I find that if you need a front-end database without the bulk and you don’t care too much about ACID compliance Mongo is the way to go. As an added bonus bringing SQL Server to bear on the exported data makes it that much more powerful for both analytics and storage.