Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Integrating MongoDB into SQL Server 2012 Enterprise Expand / Collapse
Author
Message
Posted Tuesday, June 11, 2013 11:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:45 AM
Points: 12, Visits: 85
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
Post #1462303
Posted Wednesday, August 6, 2014 7:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 3:39 AM
Points: 53, Visits: 105
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.
Post #1600228
Posted Wednesday, August 6, 2014 9:23 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 6, 2014 7:07 AM
Points: 412, Visits: 980
Not familiar with MongoDB but i'm intrigued now. Here is a possible resource:

http://docs.mongodb.org/ecosystem/drivers/csharp-community-projects/

Post #1600284
Posted Wednesday, August 6, 2014 11:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 3:39 AM
Points: 53, Visits: 105
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.
Post #1600330
Posted Wednesday, August 6, 2014 12:04 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 6, 2014 7:07 AM
Points: 412, Visits: 980
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.
Post #1600340
Posted Wednesday, August 6, 2014 12:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 3:39 AM
Points: 53, Visits: 105
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!
Post #1600345
Posted Friday, August 8, 2014 8:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:08 AM
Points: 80, Visits: 269
Try this:
Mongosis

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 to generate the classes you need from your data.
Post #1601199
Posted Friday, August 8, 2014 8:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:45 AM
Points: 12, Visits: 85
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.
Post #1601214
Posted Saturday, August 9, 2014 2:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 3:39 AM
Points: 53, Visits: 105
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.
Post #1601373
Posted Saturday, August 9, 2014 9:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:45 AM
Points: 12, Visits: 85
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.
Post #1601437
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse