TABLE OF CONTENTS

• SQL Importer Overview
  • Supported Databases
• SQL Server
• Dataverse
• MySQL
• PostgreSQL
• MongoDB
  • Example Query
• Microsoft Access Database
  • Example
• Further Support

SQL Importer Overview

This article describes features unique to the SQL Importer. For general importer features, refer to the relevant documentation.

Supported Databases

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

SQL Server

SQL Server is a relational database management system (RDBMS) designed for efficient data storage, retrieval, and management, particularly in enterprise environments.

ManagementStudio supports reading data from SQL Server databases.

Dataverse

Dataverse is a cloud-based data platform provided by Microsoft. It enables secure storage, management, and integration of data used by business applications, including those built with Microsoft Power Platform tools such as Power Apps, Power Automate, and Power BI.

ManagementStudio can connect to Dataverse to import data.

MySQL

MySQL is an open-source relational database management system widely used in web applications and enterprise solutions. Originally developed by MySQL AB and currently maintained by Oracle Corporation, MySQL is supported by ManagementStudio for data imports.

PostgreSQL

PostgreSQL (Postgres) is a powerful open-source object-relational database management system (ORDBMS), recognised for its extensibility, standards compliance, and robust feature set.

ManagementStudio can import data from PostgreSQL databases.

MongoDB

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

ManagementStudio can import data from MongoDB if the data is returned as a strict key-value pair array.

Requirements and Notes:

• Shell Requirement: The mongosh.exe shell must be accessible on the MS IIS server. Specify the folder path to mongosh.exe in the Aux Param 1 field during configuration.
  Download mongosh.exe
• Paging: Paging is not supported.
• Data Format: Returned data must be in a strict key-value array format.

Example Query

Use JSON.stringify at the beginning and .toArray() at the end of your query.

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 Database

Microsoft Access is a file-based relational database system using .mdb or .accdb files. It combines a database engine, user interface, and development tools.

ManagementStudio can import data from Microsoft Access databases if the file is accessible by the server.

Configuration Steps:

• Enter the database file path in the DB Server field.
• Enter the filename in the DB Name field.

Example

Further Support

For additional assistance, visit the ManagementStudio Service Desk to search the knowledge base or create a support ticket.