question

exonian avatar image
0 Votes"
exonian asked ·

Bug in Flat File export in large datasets.

Hi, I need to export the data from my SQL server in JSON format (so that they can imported to a graph database).

When I have few data, this is no problem, I can even run the query and copy paste the result. But this works only when the output file is roughly less than 4MB.

When I have large dumps of data - in some instances can be up to 100MB, I am using the export technique from the SQL server.

You can find the documentation in the answer here: https://stackoverflow.com/questions/952247/sql-server-truncation-and-8192-limitation For the case of JSONs you just have to tick the Unicode box.

https://docs.microsoft.com/answers/storage/attachments/74251-jsonbug1.png

Then you add your query:

https://docs.microsoft.com/answers/storage/attachments/74235-jsonbug2.png

And finally you chose your delimiters:

https://docs.microsoft.com/answers/storage/attachments/74214-jsonbug3.png

The problem is that when the size of the dump is too large, the dump is corrupted. You get odd empty lines etc. see:

https://docs.microsoft.com/answers/storage/attachments/74197-jsonbug4.png

or this:

https://docs.microsoft.com/answers/storage/attachments/74236-jsonbug5.png

At times, in very large files (over 30MB) splits/new lines appear after the opening quotes of properties/attributes etc.

Has anyone here came across this issue?

How can we get a quick resolution? Just by writing here or need to open a case will it solve the issue in a reasonable amount of time? Any other approach in exporting JSON data? Thank you!

[1]: /answers/storage/attachments/74251-jsonbug1.png [2]: /answers/storage/attachments/74235-jsonbug2.png [3]: /answers/storage/attachments/74214-jsonbug3.png [4]: /answers/storage/attachments/74197-jsonbug4.png [5]: /answers/storage/attachments/74236-jsonbug5.png

sql-server-generalsql-server-integration-services
jsonbug1.png (23.9 KiB)
jsonbug2.png (38.0 KiB)
jsonbug3.png (20.7 KiB)
jsonbug4.png (163.4 KiB)
jsonbug5.png (66.7 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered ·

Hi @exonian ,

SQL Server Integration Services (SSIS) has been used widely as an ETL tool. In addition, you can also use SSIS to export data to files such as CSV, tab delimited, Excel or XML. In this tip, we will export data in JSON format using SQL Server Integration Services.

1. In this method, we will be creating a SSIS package with a dataflow. In this data flow, we will extract data using the above-mentioned T-SQL query. The resultant JSON data content will be sent to a flat file destination.
2. This method will make use of the script task to export data in JSON format. In this method, a script task will execute the SQL query. The output of the T-SQL query will be read using C# and the result set will be written to a flat file.

Please refer to Using SQL Server Integration Services to Export Data in JSON Format.

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The output is much more improved thanks to the advice provided.

The trick here was the choice of datatype: As per the instruction you provided ,the default data type in the flat file that stores the results of the JSON was changed to Unicode text stream [DT_NTEXT] (from its default Unicode string [DT_WSTR])

I think it maybe worthwhile for Microsoft to change the default datatype in its export wizard when the user clicks unicode.

74927-jsonbug7.png


0 Votes 0 ·
jsonbug7.png (94.9 KiB)
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

Try this:

DECLARE @json nvarchar(MAX)
SELECT @json = (SELECT .... FOR JSON AUITO)
SELECT @json


I have not tested this, but there are other contexts where this workaround is needed.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

exonian avatar image
0 Votes"
exonian answered ·

Thank you but...
1. Correct the AUITO to AUTO in your post.
2. You cannot do the declaration etc. as you do in the query editor in the export package. You get this error:

74572-jsonbug6.png

It will be great if you chose to test first your advice before speculating.


jsonbug6.png (14.3 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

OK, so try this instead:

SELECT CAST ((SELECT * FROM sys.objects FOR JSON AUTO) AS nvarchar(MAX))

And, no, I have not tested this in the Import/Export wizard, because I never use it myself, and hardly know how to use it.

But I know that there are issues in SqlClient, which causes data returned with FOR JSON to be truncated, and you can work around that bug by converting to a regular nvarchar. I am not sure that you are hitting that bug, but it is worth trying.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.