SQL Importer

SQL Importer

Supported Databases

• SQL Server
• Dataverse
• MySQL
• PostgreSQL
• MongoDB
• Access

SQL Server

Dataverse

MySQL

PostgreSQL

MongoDB

MongoDB is a NoSQL, document-oriented database that stores data in flexible, JSON-like documents. It’s designed for scalability, high availability, and ease of development. 

ManagementStudio can read from a MongoDB as long as the data is returned in a strict key-value pair array format.

MongoDB requires the mongosh.exe to be accessible from the MS IIS server. Put the folder path to the mongosh.exe in the Aux Param 1 field. 

Paging is not currently supported.

Example Query

Note the 'JSON.stringify' at the beginning and the .toArray() at the end. 

JSON.stringify(db.tests.aggregate([{$project: {Name: '$Data.__value.Name', UniqueId: '$Data.__value.UniqueId', _id: 0}}]).toArray())

// Expanded for clarity
JSON.stringify(db.tests.aggregate(
[
 {
  $project: {
   Name: '$Data.__value.Name', 
   UniqueId: '$Data.__value.UniqueId', _id: 0
  }
 }
]
).toArray())

Microsoft Access DB

A Microsoft Access database is a file-based relational database system used to store, manage, and query structured data. It combines a database engine (Jet/ACE), a user interface, and development tools in a single .mdb or .accdb file. 

ManagementStudio can read from a Access DB as long as it can see the file location of the DB.

When configuring the Access Connrctor, put the path to the DB in the "DB Server" field and the file name of the DB in the "DB Name" field. 

Example: