Table FileReaderWriterJson

Description

No description available

Supported Operations

Examples

SSIS

Use OneDrive Connector in API Source component to read data or in API Destination component to read/write data:

Read from JSON File Read or Create or Update table using API Source

API Source - OneDrive

Read and write Microsoft OneDrive data effortlessly. Integrate, manage, and automate files and folders — almost no coding required.

OneDrive

JSON File Read or Create or Update

Required Parameters
Drive Id / Shared Folder	Fill-in the parameter...
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed	Fill-in the parameter...
FileCompressionType	Fill-in the parameter...
Optional Parameters
Default Group or User Id (additional Scopes needed to list - If fails enter manually)
Search Type (For UI Only - Default=Recursive - i.e. Blank)
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed
DataFormat	Notset
Continue On 404 Error (When item not found)	True
Filter
ExcludedProperties
Encoding
CharacterSet
EnableCustomReplace
SearchFor
ReplaceWith
EnableArrayFlattening
MaxArrayItemsToFlatten
ArrayTransformType
ArrayTransColumnNameFilter
ArrayTransRowValueFilter
ArrayTransEnableCustomColumns
ArrayTransCustomColumns
EnablePivot
DateFormatString

Read/write to JSON File Read or Create or Update table using API Destination

API Destination - OneDrive

Read and write Microsoft OneDrive data effortlessly. Integrate, manage, and automate files and folders — almost no coding required.

OneDrive

JSON File Read or Create or Update

Select

Required Parameters
Drive Id / Shared Folder	Fill-in the parameter...
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed	Fill-in the parameter...
FileCompressionType	Fill-in the parameter...
Optional Parameters
Default Group or User Id (additional Scopes needed to list - If fails enter manually)
Search Type (For UI Only - Default=Recursive - i.e. Blank)
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed
DataFormat	Notset
Continue On 404 Error (When item not found)	True
Filter
ExcludedProperties
Encoding
CharacterSet
EnableCustomReplace
SearchFor
ReplaceWith
EnableArrayFlattening
MaxArrayItemsToFlatten
ArrayTransformType
ArrayTransColumnNameFilter
ArrayTransRowValueFilter
ArrayTransEnableCustomColumns
ArrayTransCustomColumns
EnablePivot
DateFormatString

ODBC application

Use these SQL queries in your ODBC application data source:

Create or update a JSON file from external source

<p>Creates or updates a JSON file using a streaming approach. Reads records from an external source (e.g. Microsoft SQL Server) and writes JSON. Use <code>LayoutMap</code> to customize the JSON layout. If the file does not exist, a new one is created.</p>

INSERT INTO FileReaderWriterJson
SOURCE( 'MSSQL'--OR 'ODBC'
, 'Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true'
, 'select OrderId,CustomerId,OrderDate FROM Northwind.dbo.Orders'
)
WITH(
	    DriveId='b!XpzQciaV_k6my5II5L22J0C4iRhyz21Js89PUyZ6-w0lH0AYv_I8RJHpXZQ81efD'
	  , FileId='root:/test_out.json:'  --Path must end with colon
	--, FileId='root:/subfolder/test_out.json:'
	--, FileId='01N3NxxxxxxxxxxWZYSDJ'  --exising File ID
	--, ContinueOn404Error=0 --Fail if file not found (Useful for overwrite mode for exising file)	
	  , JsonOutputFormat='Multicontent' --Default , Array2D, ArrayLines ...
	--, DoNotOutputNullProperty='True'
	--, Encoding='UTF8' --UTF8WithoutBOM
    --, WriterDateTimeFormat='yyyy-MM-ddTHH:mm:ss.fff'
	
	--Example#1: Output all columns
	, LayoutMap='<?xml version="1.0"  ?>
<settings>
  <dataset id="root" main="True" readfrominput="True" />
  <map src="*" />  
</settings>'

	--Example#2: Nested JSON (Records under an array)
	/*
	, LayoutMap='<?xml version="1.0" ?>
<settings singledataset="True">
  <dataset id="root" main="True" readfrominput="True" />
  <map name="MyArray" dataset="root" maptype="DocArray">
    <map src="OrderID" name="OrderID" />
    <map src="OrderDate" name="OrderDate" />
  </map>
</settings>'
   */
   
	--Example#3: Nested JSON (Records under a sub document section)
	/*	
	, LayoutMap='<?xml version="1.0" ?>
<settings>
  <dataset id="dsRoot" main="True" readfrominput="True" />
  <map name="NestedSection">
    <map src="OrderID" name="OrderID_MyLabel" />
    <map src="OrderDate" name="OrderDate_MyLabel" />
  </map>
</settings>'
	*/
)

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Create or update a JSON file from external source

<p>Creates or updates a JSON file using a streaming approach. Reads records from an external source (e.g. Microsoft SQL Server) and writes JSON. Use <code>LayoutMap</code> to customize the JSON layout. If the file does not exist, a new one is created.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO FileReaderWriterJson
SOURCE( ''MSSQL''--OR ''ODBC''
, ''Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true''
, ''select OrderId,CustomerId,OrderDate FROM Northwind.dbo.Orders''
)
WITH(
	    DriveId=''b!XpzQciaV_k6my5II5L22J0C4iRhyz21Js89PUyZ6-w0lH0AYv_I8RJHpXZQ81efD''
	  , FileId=''root:/test_out.json:''  --Path must end with colon
	--, FileId=''root:/subfolder/test_out.json:''
	--, FileId=''01N3NxxxxxxxxxxWZYSDJ''  --exising File ID
	--, ContinueOn404Error=0 --Fail if file not found (Useful for overwrite mode for exising file)	
	  , JsonOutputFormat=''Multicontent'' --Default , Array2D, ArrayLines ...
	--, DoNotOutputNullProperty=''True''
	--, Encoding=''UTF8'' --UTF8WithoutBOM
    --, WriterDateTimeFormat=''yyyy-MM-ddTHH:mm:ss.fff''
	
	--Example#1: Output all columns
	, LayoutMap=''<?xml version="1.0"  ?>
<settings>
  <dataset id="root" main="True" readfrominput="True" />
  <map src="*" />  
</settings>''

	--Example#2: Nested JSON (Records under an array)
	/*
	, LayoutMap=''<?xml version="1.0" ?>
<settings singledataset="True">
  <dataset id="root" main="True" readfrominput="True" />
  <map name="MyArray" dataset="root" maptype="DocArray">
    <map src="OrderID" name="OrderID" />
    <map src="OrderDate" name="OrderDate" />
  </map>
</settings>''
   */
   
	--Example#3: Nested JSON (Records under a sub document section)
	/*	
	, LayoutMap=''<?xml version="1.0" ?>
<settings>
  <dataset id="dsRoot" main="True" readfrominput="True" />
  <map name="NestedSection">
    <map src="OrderID" name="OrderID_MyLabel" />
    <map src="OrderDate" name="OrderDate_MyLabel" />
  </map>
</settings>''
	*/
)';

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];