Export / Create JSON File in Informatica (from SQL Query / Database Tables)

Introduction

In this article, you will learn how to create simple or complex JSON file in Informatica just in a few clicks. We will use the Powerful Export JSON Command line Builder from ZappyShell. Using this Portable / xCopy style command line you can build nested/complex JSON / XML or even CSV from single / multiple SQL Queries as Input or Select Database Tables as Source. Later you can copy generated command line from Tool and execute in any tool like Informatica / Talend / DataStage or Automation Script like Python / PowerShell.

Prerequisite

This article assumes you have access to Informatica Developer Tools and basic knowledge of command-line tools.

1. Download ZappyShell Command line utility (Click on Download Button). You can get msi and Zip (Poratble / xCopy version)
2. If you want to use the Portable version just extract ZappyShell-xCopy zip to some folder lets say (C:\ZappyShell). If you installed MSI then its usually in C:\Program Files (x86)\ZappySys
3. Double click on DbCommandBuilder.exe as listed here  to get started. At the end of this exercise, you will be able to generate something like the below JSON File in few clicks in Informatica or any other Tool / Programming Language you prefer which has option to invoke command line…
4. For all possible command line options Refer this
5. So lets get started

Sample JSON Created by ZappyShell from Informatica

Sample JSON Created by Informatica Workflow (Create From Database SQL Query or Tables)

Create Command Line to Export JSON File from Database SQL Query / Tables

In this section, we will see how to build Command Line which you can then execute in Informatica Command Time and later Schedule Export Job to Produce JSON File on a predefined schedule or manually.

1. Open DbCommandBuilder.exe and configure the Connection / Data source(s) as listed here .
2. Select JSON Export Option from the Dropdown. You can also choose XML, Excel or CSV
3. You can use the Custom Layout Option if you wish to Export using custom Output Layout else just choose tables you like to export or type SQL query.
   
4. Configure your Input Dataset(s) (can be SQL Queries or Tables) and define Desired Layout for your Output File.
5. Here is just an example of how to define an Array for a nested layout in a JSON or XML file.
6. t the end of configuration you may see command line like below (more examples listed here)Example: Simple mode Export (Microsoft SQL Server)
   
   db.exe export customers --json -o c:\data\cust.json --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;" -y

   1	db.exe export customers --json -o c:\data\cust.json --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;" -y

   Example: For Simple mode Export (MySQL SQL Server – ODBC Driver)
   
   export customers --out c:\data\customers.json --json --overwrite --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name"

   1	export customers --out c:\data\customers.json --json --overwrite --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name"

   Example: For custom layout mode Export
   
   
   export --map c:\data\MySampleMap.xml -o c:\data\customers.json --json --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;" -y

   1	export --map c:\data\MySampleMap.xml -o c:\data\customers.json --json --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;" -y

7. Once the Command line is created you can click Run to test and then copy it into tools like Informatica Command Task (see next section).

Export / Create JSON File In Informatica

1. Once the command line is prepared (see previous section) we can open Informatica Developer (D)
2. Connect to your Repository and create a new workflow
3. Drag new Command Task, from Toolbox and double click to edit
   

   Informatica Command Task

4. Enter the sample command line we obtained from the previous step
5. Save the workflow and run it… That’s it you will see the JSON file generated

Export / Create XML File In Informatica

Same Steps as above except choose File format as XML and you will see output like the below once you run the command line.

Export / Create XML File In Informatica

Export / Create CSV File In Informatica

Same Steps as above except choose File format as CSV and you will see output like the below once you run the command line.

Generate CSV Files in Informatica

Export / Create Excel File In Informatica

Same Steps as above except choose File format as Excel and you will see output like the below once you run the command line.

Conclusion

In this post, we just saw how easy it is to generate JSON / XML / Excel or CSV files in tools like Informatica, Talend, python, PowerShell in few clicks without coding. You can schedule job to generate JSON / XML files from Database tables or SQL Query. Download ZappyShell Today and try it to see what else you can do with it.