SSIS Export JSON File Task

Help > Tasks > Export JSON File Task >

SSIS Export JSON File Task
SSIS Export JSON File Task can be used to generate simple or complex JSON files out of relational data source. This completely dynamic approach without worrying about changing columns inside table. We worked hard to take care most painful tasks for exporting data to JSON format.

Video Tutorial

Step-By-Step

In this section you will learn how to generate JSON data file using Export JSON Task (In this case its Default and Custom Layout).

Firstly, You need to Download and Install SSIS ZappySys PowerPack.
Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
In visual studio just Drag and Drop ZS Export JSON Task in design panel.
Now, We need OLEDB Connection For Source.

How to create OLE DB Connection.

Let's, Right click on Connection Managers Panel to Create OLEDB Connection, so you can use Source and Context Menu will appear, Select New OLEDB Connection from the Context Menu.
Now, click on New Button for create Connection.
Let's Configure Connection Manager, just Follow steps one by one as we have created.
CLick on OK button to save connection configure setting UI.

How to extract data from SQL Server Table to JSON file using Export JSON Task (Custom Layout).

Double click on ZS Export JSON Task for Configure it.
Check on Custom Layout, Make sure Check on Use Custom Layout from Source Tab to see Custom Layout Tab until it will hide from Properties.
Now go to Custom Layout tab and Add Dataset with just few clicks.
Now, Right Click on Mappings from left panel of Export JSON File task, select Add Element(s)(Below this node). And than, In Add/Edit Attribute dialogbox, select Add Single (Bound) and select column.
Now, Right Click on Mappings from left panel of Export JSON File task, select Add Value Array (Below this node). And than, configure Add/Edit Element (Array of rows) like below image.
Here, Right Click on Mappings from left panel of Export JSON File task, select Add Unbound Nested Element (Below this node). And than, In Add/Edit Attribute dialogbox, enter Output Alias name.
Now, Right Click on Location element from left panel of Export JSON File task, select Add Element(s) (Below this node). And than, In Add/Edit Attribute dialogbox for Select Add Multiple (Bound) and check on Columns.
Once you finished above steps you will see Export JSON Task like below image.
Let's, Configure in JSON Options Tab, check on Indent Output (Pretty Print).
Now, set Target Path and Other Options, Select Target Location from Browse Button. Check on Overwrite target file(s) if you want it. You can Check on Compress file to *.gz format.
Click on OK button to save Export JSON Task configure setting UI.
Here you go, Just Execute SSIS Export JSON File Task and Check Execution Results

How to extract data from SQL Server Table to JSON file using Export JSON File Task (Default Layout).

Double click on ZS Export JSON Task for Configure it.
Let's, Generate JSON files for selected tables. Set Source Connection we have created, click on Select for Select Source Table you want to export.
Now, set Target Path and Other Options, Select Target Location from Browse Button. Check on Overwrite target file(s) if you want it. You can Check on Compress file to *.gz format.
Click on OK button to save Export JSON Task configure setting UI.
Here you go, Just Execute SSIS Export JSON File Task and Check Execution Results.

How to split Data into multiple files using Export JSON File Task.

Double click on ZS Export JSON Task for Configure it.
In the Split Options Tab, check on Enable Split By Size/Rows and Click on Split By Rows. If you specify Group By Column then new file will be created for each unique value of Group By Column (e.g. If you specify Group By Column = Country then one file will be created for each Country).
Here you go, Just Execute SSIS Export JSON File Task and Check Execution Results.

How to export JSON in 2D Array format

If you have to export JSON as 2D array format then you can change following option.
SSIS Export JSON File Task - Export as 2D Array

Here are examples of output for each option. Below example assumes your source has 2 rows with 2 columns (i.e. id and name). Depending on which option you choose your output can be one of the following formats. Default

[{id:1,name:"AA"}, {id:2,name:"BB"}]

Multicontent

{id:1,name:"AA"}{id:2,name:"BB"}

ArrayDocs

[{id:1,name:"AA"}, {id:2,name:"BB"}]

Array2D

[[1,"AA"],[2,"BB"]]

Array2DWithHeader

[["id","name"],[1,"AA"],[2,"BB"]]

ArrayLines

[1,"AA"][2,"BB"]

ArrayLinesWithHeader

["id","name"][1,"AA"][2,"BB"]

Properties

Property Name

Description

JsonSupportMultiContent

Output records in Multi content json where each json record is appended to file without comma and opening closing array brackets

DoNotOutputNullProperty

Do not output property if value is NULL. By default NULL value also output in file (e.g. {"Email": null} )

DateFormat

Date format in csv data file (e.g. yyyy-MM-dd HH:mm:ss.fff)

Encoding

Encoding of source file

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option	Description
Default [0]	Default
ASCII [1]	ASCII
UTF8 [2]	UTF-8
UTF16 [3]	UTF-16 LE (i.e. Unicode Little Endian)
UTF32 [4]	UTF-32
UTF8WithoutBOM [5]	UTF-8 Without BOM
UTF32WithoutBOM [6]	UTF-32 Without BOM
UTF7 [7]	UTF-7
UTF7WithoutBOM [8]	UTF-7 Without BOM
UTF16WithoutBOM [9]	UTF-16 Without BOM
BigEndian [10]	UTF-16 BE (i.e. Unicode Big Endian)
BigEndianWithoutBOM [11]	UTF-16 BE Without BOM

Timeout

Time out for source query. 0 means no timeout

Connection

Connection for source table/query from which you want to export data. This can be valid ADO.net or OLEDB connection Manager Name or ID

Source

Source for the data. This can be table(s), query, view(s) or proc. If its other than table or view then you must specify --proc or --query parameter to indicate access mode. You may list multiple objects by separating them using vertical bar (e.g table1|table2|view1). You can also use pattern for objects (e.g. cust%|prod%)

Schema

Schema name or pattern to search for objects when object name contains pattern (e.g. Cust%)

ExcludeViews

If pattern used on object name then do not include matching views in the objects to export.

ExcludeTables

If pattern used on object name then do not include matching views in the objects to export.

UseMappingFile

Use custom layout and additional processing instructions per column

MappingAccessMode

AccessMode for Mapping file. This determines how mapping information will be read at runtime

MappingDirectValue

Mapping information in the format of XML

MappingVariable

Variable which holds mapping information

MappingFilePathVariable

Variable which holds mapping file path

MappingFilePath

Mapping file path which contains custom layout and additional information

Target

Target file or folder path

Append

Append data at the end of file

Overwrite

Overwrite target file if exists

GroupByColumn

Split files by group by column value. e.g. if you group by Country field then you will have one file for each country. Country name will be used as file name suffix. If you specify --split option then you may have more than one file for each value in group by field depending on split size/rows you specify

Compress

Compress file in gzip format

KeepRaw

Keep source file after compressing it when --gzip option enabled. By default source file is deleted once its compressed.

SplitFiles

Split into multiple files (when split enabled you can split by filesize or rowcount. Check --splitsize and --splitrows options).

MaxSizePerFile

Maximum size per file. You can specify in bytes,kb,mb or gb. Example: 10mb, 5000kb, 1gb, 2000bytes

MaxRowsPerFile

Maximum rows per file

MaxRowsToExport

Maximum number of rows to export.

LoggingMode

LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.

Setting UI

Articles / Tutorials

Click here to see all articles for [SSIS JSON Export Task] category

SSIS Data Load Ã¢ÂÂ SQL Server to FTP/SFTP (Split Files, GZip)

Introduction In this blog post you will see how easy it is to load large amount of data from SQL Server to FTP/SFTP. SSIS Data Load Ã¢ÂÂ SQL Server to FTP/SFTP (Split Files, GZip). For demo purpose we will use SQL Server as relational source but you can use same steps for any database engine […]

5 Ways to Export JSON from SQL Server using SSIS

Introduction SQL Server 2012ÃÂ and all previous versions lacking native JSON support, same theory is true for their ETL Platform SSIS. If you are one of them who wants to export JSON from SQL Server or any other RDBMS like MySQL, Oracle then you can easilyÃÂ output complexÃÂ JSON using any of the following components. Five ways of […]