In my last post, I was stymied by a problem in SSIS. My goal was to use a T-SQL statement to generate a set of rows to represent each NHL season since 1917 as JSON and then to store the JSON in a flat file.
Specifically, this T-SQL statement:
DECLARE @seasonStart INT=1917; DECLARE @seasonEnd INT=year(getdate()); WITH seasonYears AS ( SELECT @seasonStart AS seasonYear UNION ALL SELECT seasonYear+1 FROM seasonYears WHERE seasonYear+1<=@seasonEnd ) SELECT convert(varchar, seasonYear) + convert(varchar, seasonYear + 1) as season FROM seasonYears option (maxrecursion 200) for json path, root('seasons');
What happened? The T-SQL produces the correct results in SQL Server Management Studio (SSMS). However, in SSIS, the same T-SQL statement in an OLE DB Source in a Data Flow Task produces two rows of data which adds a line feed into the flat file and renders the JSON unusable.
The problem is visible even before sending output to the flat file. Here’s what I see when I preview the query results for the OLE DB Source:
I wound up just using SSMS to generate the JSON and using copy/paste to create the file I needed because that got the job done. Meanwhile, I learned a bit more about why I ran into this problem, and I came up with a solution that I want to share with you in this post.
Official word from Microsoft is that FOR JSON always returns output in 2-4KB chunks. Although SSMS doesn’t display these chunks in separate rows, SSIS handles it differently. I could solve this problem either by using a Script Component as a source – and that would mean I would have to write code, which I don’t want to have to do if I don’t have to. Or I could handle the T-SQL in a different way. Which I did.
The trick is to store the JSON output in a variable and then output the result of the variable. I wound up having to rewrite the T-SQL statement because I couldn’t use a CTE in the statement assigned to the variable. Here’s the revised statement that returns the same results as the original shown at the beginning of this post.
DECLARE @seasonStart INT=1917; DECLARE @seasonEnd INT=year(getdate()); DECLARE @jsonText NVARCHAR(max) = (SELECT CONVERT(VARCHAR(4), @seasonStart + seasonYears) + CONVERT(VARCHAR(4), @seasonStart + seasonYears + 1) AS season FROM (SELECT number AS seasonYears FROM master..spt_values WHERE type = 'p' AND number BETWEEN 0 AND @seasonEnd - @seasonStart) AS t FOR JSON PATH, ROOT('seasons')); SELECT @jsonText AS jsonOutput;
When I preview this query in SSIS, I get one row of output:
And when I produce the flat file, I get the file I wanted. Problem solved!