How to parse empty JSON Array in SSIS JSON Parser Transform

Problem

By default JSON Parser Transform or XML Parser Transform will SKip Parsing document if Input Column has NULL or Blank value (Skipping the row). But what if you want to output NULL rather than Skipping rows?

Input Rows 

[{Id:1, Name:"AAA"}]
[{Id:2, Name:"BBB"}]
NULL
[{Id:3, Name:"DDD"}]

Output From JSON Parser (Notice NULL is skipped)

Id | Name
----------
1  | AAA
2  | BBB
3  | DDD

 

Workaround

Workaround is simple. Use Derived column transform before JSON Parser Transform like below.

NOTE: This workaround may not be good if your array may contain more than one documents because with this workaround you always get one NULL Row for empty array but if you think its good enough for your case continue with this option.

Use Derived Column Transform with Replace Column Action. You can use any of the below expression based on your case.

For example if you processing JSON with starts with "[" (Array format) then use something like below 

Data == "[]" ||  LENGTH( Data )==0  ? "[{}]" : Data

---OR-- Use below if Document input is in DT_NTEXT type (More than 4000 chars)

Data == (DT_NTEXT)"[]" ||  ISNULL( Data ) ? (DT_NTEXT)"[{}]" : Data

If JSON with starts with "{" (Single Document) then use something like below 

LENGTH(Data)==0 ? "{ }" : Data

---OR-- Use below if Document input is in DT_NTEXT type (More than 4000 chars)

Data==(DT_NTEXT)"" || ISNULL( Data ) ? (DT_NTEXT)"{}" : Data

Contact Us

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

https://zappysys.com/support/