June 11, 2013 at 11:55 am
I need to integrate a MongoDB into SQL 2012 Enterprise environment. The main obstacle is that the JSON structure is quite complex with nested documents and arrays. The arrays could have an infinite number of records.
I’ve been reviewing 3rd party solutions to build just the Mongo to SQL ETL and yet to find an ideal solution.
Are there any words of wisdom or suggestions…other than replacing the MongoDB with SQL Server:-P
August 6, 2014 at 7:35 am
Hi,
Did you get a reply on how to do it? I am facing the same problem, need to export my mongodb db into sql server to make a dw and I am loosing it. :w00t:
August 6, 2014 at 9:23 am
Not familiar with MongoDB but i'm intrigued now. Here is a possible resource:
http://docs.mongodb.org/ecosystem/drivers/csharp-community-projects/
August 6, 2014 at 11:44 am
hi,
i know you can import mongo to csv, and probably grab the csv using ssis
but the problem is the architecture of mongo, which as may see i dont understand fully
i think (probably making it up) you can actually export mongo directly using ssis with a script or something.
i am truly lost.
August 6, 2014 at 12:04 pm
Yes I did see the export command but to automate this that may be more of a problem, I guess it depends how much control you have over the mongo db and if you can setup jobs on the Mongo side.
Here is another link from the mongo site for the C# driver:
http://docs.mongodb.org/ecosystem/drivers/csharp/
I have not reviewed the code but worth downloading... you could build a dll that can then be referenced in a Script Component in SSIS setup as a Data Source.
August 6, 2014 at 12:08 pm
i will check it out and let you know how it went, i am also trying an oldb connector. i managed to connect, but that is as far as i got.
hope to have a lot of good news to share soon!
August 8, 2014 at 8:14 am
Try this:
Be sure to check out the requirements and get version 1.7.0 of the MongoDB C# driver. The component can be a little slow, and due to the nature of MondoDB it has to guess at datatypes so most things end up coming through as VARCHAR(100) so you'll need to keep an eye out for truncation. I'm not sure how it'll handle nested documents, as I don't think I came across those the last time I used it.
At the very least it might give you a starting point.
Failing that you can use the C# driver in a script component, along with json2csharp[/url] to generate the classes you need from your data.
August 8, 2014 at 8:40 am
I spent a lot of time researching this and tested a handle full of third-party ETL packages. Mongosis would have been a great choice but at the time of testing did not support nested JSON.
My current overall recommendation based on functionality and value is Clover ETL. I find it hard to justify the cost of an additional ETL to use along side SSIS.
August 9, 2014 at 2:25 am
well it is a small database and it is being just tested, since i needed a solution first thing next week i will do simple java which i haven't written in 12 year 😀
but i will let you all know when i finally get it done.
:w00t::w00t::w00t:
August 9, 2014 at 9:11 am
Clover does give you a trial time to use their tool. There is a learning curve with manually setting up the metadata and mappings but they may have resolved some of those issues since I used it. Good luck with your project.
August 5, 2015 at 2:17 am
Hello,
We have just released MongoDB Connectors for SSIS. We have implemented custom SQL query engine so user doesn't have to learn totally new way to query MongoDB. I have complete package with sample examples Here I would like to mention some featutres
MongoDB Source
1.Fast data extract from MongoDB collection.
2.Support for SQL query language (Proprietary parsing engine translates SQL query into MongoDB query).
3.Support for SQL Server 2005, 2008, 2012, 2014 (32 bit and 64 bit)..More about MongoDB Source[/url]
SSIS MongoDB Destination
1.Bulk insert data to MondoDB collection.
2.Option to define batch size for efficient data insert....More about SSIS MongoDB Destination[/url]
August 11, 2015 at 2:59 am
Hello,
I have got the relevant posts and and well familiar working with "SSIS mongodb". This post will really helpful for you. Here I would like to mention some point regarding SSIS mongodb and You follow the link for more detail about the task.
1.Bulk extract MongoDB data using SSIS Custom Adapter
2.Support for SQL Query for MongoDB. No need to learn new json style query language from MongoDB
3.Support for SQL Server 2005, 2008, 2012, 2014 (32 bit and 64 bit).:-)
August 12, 2015 at 9:38 pm
In an SSIS 2012 Data Flow Task, you could use a Script Component (setup as a Source) to get a JSON string from Mongo DB and deserialize it into .NET objects:
.NET 4.0 JavaScriptSerializer Class
I have no notion of what a connection to MongoDB from within a Script Component might look like or what a MongoDB query looks like but with the C# MongoDB driver[/url] and their system documentation it seems feasible.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 13, 2015 at 7:30 am
Your MongoDB components look very interesting and if I ever need to pull from Mongo again, I will test them and see how they do. Hopefully, I won’t see Mongo again, but I’m sure our paths will cross.
It was fun testing and playing with various tools and I did build a successful solution. However, the overall project fizzled and the solution never made it into production.
Note to others that find themselves in the same position…please stress to the app devs that just because you don’t need relational structure in MongoDB, it doesn’t mean to allow everything to run amok and see how many nested layers of JSON you can throw in a document and then expect to build operational and analytical reports. If you need you need these types of reports…Mongo may not be the ideal solution for this data.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply