SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Integrating MongoDB into SQL Server 2012 Enterprise


Integrating MongoDB into SQL Server 2012 Enterprise

Author
Message
Sir Hossfly
Sir Hossfly
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 90
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 
astrid 69000
astrid 69000
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 200
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
Tom Van Harpen
Tom Van Harpen
SSC Eights!
SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)

Group: General Forum Members
Points: 880 Visits: 1102
Not familiar with MongoDB but i'm intrigued now. Here is a possible resource:

http://docs.mongodb.org/ecosystem/drivers/csharp-community-projects/
astrid 69000
astrid 69000
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 200
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.
Tom Van Harpen
Tom Van Harpen
SSC Eights!
SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)

Group: General Forum Members
Points: 880 Visits: 1102
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.
astrid 69000
astrid 69000
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 200
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!
jonharding2204
jonharding2204
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 307
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.
Sir Hossfly
Sir Hossfly
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 90
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.
astrid 69000
astrid 69000
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 200
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 :-D
but i will let you all know when i finally get it done.
w00tw00tw00t
Sir Hossfly
Sir Hossfly
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 90
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search