SSIS 2012 does not carry inbuilt provider for NoSql databases like MongoDB. But if you want to import/export data from/to MongoDB, following approach can be used.
Requirement.: We have a Comments table in our sql server database. We will export content of this table to MongoDB Comments collection.
Before creating SSIS package for export data to MongoDB , download mongoDB dlls for c# from following URL.
https://github.com/mongodb/mongo-csharp-driver/releases/download/v1.8.3.9/CSharpDriver-1.8.3.zip
Unzip the downloaded file and add following dlls in GAC.
- MongoDB.Driver.dll
- MongoDB.Bson.dll
Simply drag and drop these dlls in GAC (%windir%/assembly)
Create a database in MongoDB and add a collection. Check the no. of records in new Collection.
Open SSIS and add a new package. Add a Data Flow Source in the package. Double click this DFT and add one OLE DB Source to import Comments from Sql Server.
Add a script component as a destination to the package and connect it with above OLE DB source. Double click on script component and click on Edit Script.
Add reference for MongoDB.Driver and MongoDB.Bson dlls which have been added in GAC. Add namespace for these dlls to the script.
Add following script to insert every Sql comment record in MongoDB.
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
var mongoConnString = “mongodb://localhost”;
var mongoClient = new MongoClient(mongoConnString);
var mongoServer = mongoClient.GetServer();
var mongoDB = mongoServer.GetDatabase(“SampleMongo”);
MongoCollection Comments = mongoDB.GetCollection(“Comments”);
var comment = new comment { RespondentID = Row.RespondentID, Name = Row.Name, Comment = Row.CommentText.ToString() };
Comments.Insert(comment);
}
public class comment
{
public Int64 RespondentID { get; set; }
public string Name { get; set; }
public string Comment { get; set; }
}
You can change connectionstring and DB name according to your requirements.
Close script page and execute package. Package will successfully export data from sql server to MongoDB.
You can check no. of records in MongoDB.
To Import data from MongoDB you would need script component as source. Following script can be used to read data from MongoDB and add fields as output.
public override void CreateNewOutputRows()
{
var mongoConnString = “mongodb://localhost”;
var mongoClient = new MongoClient(mongoConnString);
var mongoServer = mongoClient.GetServer();
var mongoDB = mongoServer.GetDatabase(“SampleMongo”);
var cursor = mongoDB.GetCollection(“Comments”).FindAll();
foreach (BsonDocument mongoDocument in cursor)
{
Output0Buffer.AddRow();
Output0Buffer.ID = mongoDocument[“RespondentID”].ToInt64();
Output0Buffer.name = mongoDocument[“Name”].ToString();
Output0Buffer.comment = mongoDocument[“Comment”].ToString();
}
}
There is no inbuilt provider in SSIS to interact with MongoDB. But using powerful SSIS Script component we can easily import/export to MongoDB.
Easy BI 