SSIS/MongoDB Connectivity?
2,651 views
Skip to first unread message
PPSA
Jan 18, 2012, 6:05:57 PM1/18/12
to mongodb-user
I need to write an SQL Server Integration Services package that takes
data in an SQL Server database and puts it into a MongoDB. Are there
MongoDB Destinations that can be installed into SSIS? Other way of
doing this? Examples would be nice.
If connectivity with SSIS is poor, is there another tool/way of moving
data from SQLServer to MongoDB; hopefully in a similar way that allows
processing of the data?
Thanks.
data in an SQL Server database and puts it into a MongoDB. Are there
MongoDB Destinations that can be installed into SSIS? Other way of
doing this? Examples would be nice.
If connectivity with SSIS is poor, is there another tool/way of moving
data from SQLServer to MongoDB; hopefully in a similar way that allows
processing of the data?
Thanks.
Bill Hayward
Jan 19, 2012, 3:16:38 PM1/19/12
to mongodb-user
The following link provides some information related to moving data
from an SQL Server DB to MongoDB. Does this help you?
http://stackoverflow.com/questions/4372630/convert-table-from-sql-to-mongodb
from an SQL Server DB to MongoDB. Does this help you?
http://stackoverflow.com/questions/4372630/convert-table-from-sql-to-mongodb
PPSA
Jan 21, 2012, 8:14:34 PM1/21/12
to mongodb-user
OK, thanks. I take it there no way to do it in SSIS, then?
On Jan 19, 1:16 pm, Bill Hayward <b...@10gen.com> wrote:
> The following link provides some information related to moving data
> from an SQL Server DB to MongoDB. Does this help you?
>
> http://stackoverflow.com/questions/4372630/convert-table-from-sql-to-...
> > I need to write an SQL ServerIntegrationServicespackage that takes
On Jan 19, 1:16 pm, Bill Hayward <b...@10gen.com> wrote:
> The following link provides some information related to moving data
> from an SQL Server DB to MongoDB. Does this help you?
>
> > I need to write an SQL ServerIntegrationServicespackage that takes
Sam Millman
Jan 22, 2012, 8:27:27 AM1/22/12
to mongod...@googlegroups.com
There isnt one yet as far as I know. If you want integration with SSIS you will have to write it for yourself atm.
Tbh most people actually start again when they schema design in Mongo so this is not an extremely popular thing to do.
Tbh most people actually start again when they schema design in Mongo so this is not an extremely popular thing to do.
--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com.
To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
DaveC
Jan 22, 2012, 5:22:02 PM1/22/12
to mongodb-user
You're going to want to creat objects and insert them into MongoDB
collections via the C# driver, and the easiest way to do that in SSIS
would be through a Script Component: http://msdn.microsoft.com/en-us/library/ms136118.aspx
The tricky part about this is that quite often you will want to
combine data from several tables into a single document in MongoDB,
but I'll try to walk through some of the scenarios herein.
Add a script component to your SSIS project, and in that component,
override the ProcessInputRows method as demonstrated here:
http://msdn.microsoft.com/en-us/library/ms187303.aspx
For each row (each call from SSIS to ProcessInputRows), you'll want to
insert a document into some MongoDB collection, so create a class with
properties for all the fields in the row and handles MongoDB specifics
(like the id field). Then use
MongoServer.Create(connectionString).GetDatabase(dbName).GetCollection<T>(collectionName).Save(obj)
for each row input you're processing. Make sure to include the PK
field from the row in the SQL DB if you've got other tables that
you'll be importing that need to reference it.
Of course, in doing this, you'll end up with flat collections, just
like MSSQL tables, and I think you'll prefer to have the complex
structure provided by documents for including subproperies within your
documents. For example, in SQL Server, you might have a table of
people and have another table where you store each person's list of
phone numbers. In MongoDB, you probably want to store the phone
numbers in the same document as the person, rather than a separate
table as you might in SQL Server.
To get this, at later steps in your data flow after importing the
people, you will want to add additional script components that look up
the document for each person and set a field (e.g. PhoneNumbers) in
that document to an array, pushing a new phone number for each phone
number coming from the MSSQL source. To do so, you'll do something
like this in the ProcessInputRows for each row of people's phone
numbers (using a people collection):
people.Update(Query.EQ("PersonID", personId),
Update.Push("PhoneNumbers", phoneNumber));
I recommend creating List properties on your root document objects for
any of these sub collections so when you create the initial document,
the property for the subcollection will be initialized to an empty
list. For example, when you save the initial Person object, make sure
it has a propery Person.PhoneNumbers that is initialized to an empty
List. Then you won't have to worry with creating the PhoneNumbers
field in the document as you are processing each phone number...you
can safely call Push.
I hope that helps. If you run into issues, feel free to post relevant
parts of your code and stack trace.
-Dave
collections via the C# driver, and the easiest way to do that in SSIS
would be through a Script Component: http://msdn.microsoft.com/en-us/library/ms136118.aspx
The tricky part about this is that quite often you will want to
combine data from several tables into a single document in MongoDB,
but I'll try to walk through some of the scenarios herein.
Add a script component to your SSIS project, and in that component,
override the ProcessInputRows method as demonstrated here:
http://msdn.microsoft.com/en-us/library/ms187303.aspx
For each row (each call from SSIS to ProcessInputRows), you'll want to
insert a document into some MongoDB collection, so create a class with
properties for all the fields in the row and handles MongoDB specifics
(like the id field). Then use
MongoServer.Create(connectionString).GetDatabase(dbName).GetCollection<T>(collectionName).Save(obj)
for each row input you're processing. Make sure to include the PK
field from the row in the SQL DB if you've got other tables that
you'll be importing that need to reference it.
Of course, in doing this, you'll end up with flat collections, just
like MSSQL tables, and I think you'll prefer to have the complex
structure provided by documents for including subproperies within your
documents. For example, in SQL Server, you might have a table of
people and have another table where you store each person's list of
phone numbers. In MongoDB, you probably want to store the phone
numbers in the same document as the person, rather than a separate
table as you might in SQL Server.
To get this, at later steps in your data flow after importing the
people, you will want to add additional script components that look up
the document for each person and set a field (e.g. PhoneNumbers) in
that document to an array, pushing a new phone number for each phone
number coming from the MSSQL source. To do so, you'll do something
like this in the ProcessInputRows for each row of people's phone
numbers (using a people collection):
people.Update(Query.EQ("PersonID", personId),
Update.Push("PhoneNumbers", phoneNumber));
I recommend creating List properties on your root document objects for
any of these sub collections so when you create the initial document,
the property for the subcollection will be initialized to an empty
list. For example, when you save the initial Person object, make sure
it has a propery Person.PhoneNumbers that is initialized to an empty
List. Then you won't have to worry with creating the PhoneNumbers
field in the document as you are processing each phone number...you
can safely call Push.
I hope that helps. If you run into issues, feel free to post relevant
parts of your code and stack trace.
-Dave
Sql Daddy
Feb 17, 2016, 3:49:22 PM2/17/16
to mongodb-user
Hello PPSA,
I am having SSIS Mongodb destination .that can be installed into SSIS . How to work with It, Yhese all things is discussed in well proper way . I am sharing URL of task where all the things is discussed. Please Have a look.
If you need more help, Please let me know.
Reply all
Reply to author
Forward
0 new messages