ssis output json file adding extra crlf

问题:

This has been solved with c# code, please refer to this post the post

I have package inside a ole db source to run a query to generate Json file for a flat file destination. The query: Note I have used replace to get rid of crlf if they do exist in the column activity (which i don't see)

SELECT  
Replace(Replace([activity], char(13),''), char(10),'') [activity]
FROM [CRM].[dbo].[JJVCACUProductElectron] for json auto

The generated json is with some crlf so can see multiple lines if viewed in notepad ++. And when paste the content into a online json parser, the format is invalid. After manually removing all the crlf from the generated josn file so the json data will be in one single line then the format is fine in the parser.

Is is weird that if I change the query to just return one record into the file then the json is fine. There is only 7 rows in the table, so I have tried every row and they all work, which makes me doubt if crlf really is the issue. But if without this column, then all the rows can be generated to json no issue.

I have tried the way of use a variable to store the content of the query then select the variable, this will work but for tables with a tens of millions of rows it will cause performance issue. So this will not be an option.

In conclusion:

1. I can't use the put the content into a variable then output method.
2. Removing crlf on the column seems not working, or there is indeed no crlf in the column.
3. Removing crlf from the generated text file will work. But where do these crlf coming from?

This is select activity from [the table]

This shows the generated json file, you can see there are two lines.

And a crlf at the end of the first line. if remove this crlf so there will be just one line, then the content is a valid json.