Monday, May 11, 2015

Migarting from SQL to JSON using DocumentDB migration tool

Recently Microsoft made it easy to migrate data form certain sources to DocumentDB on Microsoft Azure. Review post here.

The following source data can be migrated to DocumentDB using the migration tool:
  • CSV file or files
  • JSON file or files
  • SQL
  • MongoDB
  • DocumentDB

  • This post shows how you may obtain response to your SQL query in JSON format using the migration tool.

    Although it seems it is not supported on Windows 8.1 from download documents, the present post was created using the DocumentDB run on a Windows 8.1 machine. The download details are here.

    The following steps show how the SQL response is obtained in JSON format, a format more suitable for Internet of Things.

    This screen shows up after launch

     
    Specifying source information. Source chosen here is SQL.

    Source of SQL is an instance of SQL Server 2012 on the local machine and the query is run against the Northwind database. The connection string is OK except it is case sensitive. Type it exactly as shown. Trying to get to read from a .sql file on the drive.


    Locating the file on the drive.

     
    Just before processing.

     
    Processing in progress.

     
    The above process failed.
    -------------------------------------
    New Process to import, this time providing a query as shown.

     
    Providing location for the response from processing.



    Details of processing.

    Processing completed, zero errors.

     
    Sample of response from the migration tool at the specified file location (only a small part is reproduced)
     
     
    SQL Comments are ignored.
     
    Happy migration!