A little over a year ago I saw a presentation on NoSQL (i.e. Not Only SQL), and specifically MongoDB. MongoDB is an open source document oriented database, and like many things related to NoSQL it seems to have a buzz about it. In this blog post I will demonstrate using SSIS and MongoDB.
The Basics
I am not going to detail installing MongoDB and related drivers, the MongoDB already does fair job and I recommend following their documentation. For the purposes of this demonstration I installed MongoDB and the C# drivers.
Once the MongoDB prerequisites have been installed, create an SSIS package and add a Data Flow. In the data flow I added a flat file source that will extract a list of countries from a CSV file. Then add a Script Component and select “Destination” as the Script Component Type.
Next open the script editor and add the following references and usings:
- MongoDB.Bson
- MongoDB.Driver
The Code
I created a Country class as part of my ETL, but this is not necessary and I would refer one to the C# driver tutorial for other approaches. In a pretty concise manner, I was able to connection the server, database and add the country data.
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using MongoDB.Bson;
using MongoDB.Driver;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void InputCountry_ProcessInputRow(InputCountryBuffer Row)
{
var connectionString = "mongodb://localhost";
var mongo = MongoServer.Create(connectionString);
var db = mongo.GetDatabase("MyMongoDb");
MongoCollection<Country> countries = db.GetCollection<Country>("Countries");
var country = new Country { CountryCode = Row.CountryCode, CountryName = Row.CountryName };
countries.Insert(country);
}
}
public class Country
{
public string CountryCode { get; set; }
public string CountryName { get; set; }
}
In this demonstration I showed how to use SSIS and create an ETL with MongoDB. This turned out pretty easy as I was able to download, install, and create this proof of concept on a Sunday. Not to mention juggling other weekend chores.
