SSIS Export JSON File Task
PreviousNext

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.

Download SSIS PowerPack

Content

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).
  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
  3. In visual studio just Drag and Drop ZS Export JSON Task in design panel.
    SSIS Export to JSON File Task - Drag and Drop
  4. Now, We need OLEDB Connection For Source.

How to create OLE DB Connection.

  1. 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.
    SSIS Export to JSON File Task - Connection
  2. Now, click on New Button for create Connection.
    SSIS Export to JSON File Task - Connection
  3. Let's Configure Connection Manager, just Follow steps one by one as we have created.
    SSIS Export to JSON File Task - Connection
  4. 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).

  1. Double click on ZS Export JSON Task for Configure it.
  2. 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.
    SSIS Export JSON File Task - JSON Layout Editor
  3. Now go to Custom Layout tab and Add Dataset with just few clicks.
    SSIS Export JSON File Task - JSON Layout Editor
  4. 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.
    SSIS Export to JSON File Task - Add Element
  5. 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.
    SSIS Export to JSON File Task - Add Array Value
  6. 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.
    SSIS Export to JSON File Task - Add Unbound Nested Element
  7. 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.
    SSIS Export to JSON File Task - Add Nested Element
  8. Once you finished above steps you will see Export JSON Task like below image.
    SSIS Export JSON File Task - JSON Layout Editor
  9. Let's, Configure in JSON Options Tab, check on Indent Output (Pretty Print).
    SSIS Export JSON File Task - JSON Date Formatting, Encoding Options
  10. 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.
    SSIS Export JSON File Task - Select Target Path and other options
  11. Click on OK button to save Export JSON Task configure setting UI.
  12. Here you go, Just Execute SSIS Export JSON File Task and Check Execution Results
    SSIS Export JSON File Task - Custom Layout Execute

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

  1. Double click on ZS Export JSON Task for Configure it.
  2. 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.
    SSIS Export to JSON File Task - Generate JSON files for selected tables/views
  3. 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.
    SSIS Export JSON File Task - Select Target Path and other options
  4. Click on OK button to save Export JSON Task configure setting UI.
  5. Here you go, Just Execute SSIS Export JSON File Task and Check Execution Results.
    SSIS Export JSON File Task - Execution Log

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

  1. Double click on ZS Export JSON Task for Configure it.
  2. 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).
    SSIS Export JSON File Task - Split JSON Data into Multiple files
  3. Here you go, Just Execute SSIS Export JSON File Task and Check Execution Results.
    SSIS Export JSON File Task- Split Option Execute

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 File encoding Type (e.g. ASCII, UTF8...)
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

SSIS Export to JSON File Task - Setting UI
SSIS Export to JSON File Task - Setting UI

See Also

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)

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

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 […]



Copyrights reserved. ZappySys LLC.