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

Srinika Pinnaduwage · Answer 1 · 2020-07-17T21:29:21.210

0

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

edited Jul 17 '20 at 21:29

answered Jul 17 '20 at 21:16

Srinika Pinnaduwage

1,044
1
7
14

Thank you for your response. But I am not looking for Java script.i am looking if we can do it in SSIS or sql – hrishi Jul 23 '20 at 19:01
@hrishi, my friend, first you should read the answer fully. The solution given here is SQL. Did you try it? – Srinika Pinnaduwage Jul 23 '20 at 20:08

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

1 Answers1