How to process 2D JSON Array without column names in SSIS

ZappySys offers a wide range of options to process JSON Array including 2D array. Check this article to learn.

However, there will be a time when you will get an odd format such as below. In this example, JSON does not have column names (i.e. we expected  OrderID, CustomerID, OrderDate, and OrderTotal somewhere but not listed). This is a perfectly valid format but it cant be processed by ZappySys SSIS Connector because of missing column names.

[
 [10248,"VINET","1996-07-04T00:00:00", 2000.50], 
 [10249,"TOMSP","1996-07-05T00:00:00", 5000.30],
 [10249,"HANAR","1996-07-08T00:00:00", 1000.00]
]

In this article, we will learn how to process 2D JSON Array when column names are not there.

Step-1: Store the JSON string in the variable.

If you are getting this type of json string in the API Response, for that you need to use Rest API Task and store the response in the string type variable.

Pass some same structured sample json string as that vAPIResponse variable value.

[
[10248,"VINET","1996-07-04T00:00:00", 2000.50], 
[10249,"TOMSP","1996-07-05T00:00:00", 5000.30],
[10249,"HANAR","1996-07-08T00:00:00", 1000.00]
]

Step-2: Configure JSON Source under Data flow task to read JSON Array.

Need to configure the JSON Source like this:

1. Select Access Mode as Direct Value

2. Enter JSON String like this, need to enter desired variable placeholder which holds the API response:

{
  "rows": {{User::vAPIResponse}}
}

3. Select The $.rows[*] as Array Filter

$.rows[*]

4. Go to Array Transform Tab 

5. Select Transform Type as Simple - 2D Array, check mark Specify columns list manually and pass the desired comma-separated column names. i.e. in this case we need to add below column names.

OrderID,CustomerID,OrderDate,OrderTotal

6. That's it, you will get the desired output on Preview as well as while you run the package.

For more information refer below link to the Parse JSON array in SSIS or ODBC Drivers: 

https://zappysys.com/links/?id=10090

 

Contact Us

If you have more questions (s) feel free to contact us via Live chat or email at [e-mail]

https://zappysys.com/support/