How to convert string which is output of select .....from for JSON AUTO to JSON Formatted file which is readable without using third party tool SSIS

Question

HI I am writing SSIS package to get the output from Select a,b,c from CUST FOR JSON AUTO However I am getting output in a single row like

{"a":"Rock" ,"b":"paper" ,"c":"scissors"}, {"a":"Rock" ,"b":"paper" ,"c":"scissors"}, {"a":"Rock" ,"b":"paper" ,"c":"scissors"}....

However I want output as

{ 
 "a":"Rock",
  "b":"paper",
  "c":"scissors"
 },
 
{ 
 "a":"Rock",
  "b":"paper",
  "c":"scissors"
 },
 
{ 
 "a":"Rock",
  "b":"paper",
  "c":"scissors"
 },

My client argument is the Json file will be big file and he don't want to do extra formatting and should be readable

Answer 1

If you can add a javascript component to SSIS, it can be done. (I'm not sure on how to do that in SSIS). But here is how you do it in javascript

I have a way to get this done rather messy way, but you can see whether that serves you the purpose

DECLARE @nl varchar(2) = char(13), @Json nvarchar(MAX) = (Select a,b,c from #CUST FOR JSON AUTO) 

Select @Json = Replace(@Json, '[{','[' + @nl + '{' + @nl) 
Select @Json = Replace(@Json, '{','{' + @nl) 
Select @Json = Replace(@Json, '","','",' + @nl + '"' ) 
Select @Json = Replace(@Json, '"},{','"' + @nl + '},' + @nl + @nl + '{' ) 
Select @Json = Replace(@Json, '"}]','"' + @nl + '}' + @nl + ']' + @nl + @nl) 

Select @Json as FormattedJSON

And here is the fiddle with more improvements