Approaches to Import JSON in SSIS (SQL Server 2016+) Part 2

Question

Approaches to Import JSON in SSIS (SQL Server 2016+) Part 2

Nikolai Basov

SSC Enthusiast

Points: 129
More actions
August 27, 2018 at 12:03 am

#367568

Comments posted to this topic are about the item Approaches to Import JSON in SSIS (SQL Server 2016+) Part 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

JimBuckley-RG Old Hand Points: 377 More actions · Answer 1

I am trying to use the Row-by-Row Flat File method but kept running into the same problem, we get hundreds of JSON documents to load each day and I don't have the ability to edit them to remove the square brackets and end curly brackets. My initial approach was to use the SQL JSON command method but our security is very strict and the SSIS account used by the server (or even mine locally) does not allow use of bulk load commands. I was really hoping not to use JSON.net as our security team is very hesitant about adding 3rd party components, so the third option seemed the best option to me. The structure of my JSON documents is fairly straightforward with one element (called contacts) that can be multi-valued (e.g. { "issueType":"VALUE", "venue":"BLDG NAME", "title":"NAME OF ISSUE", "incidentDate":"YYYY-MON-DD", "contacts":[ {"name":"NAME", "type":"TYPE"},{},{}]} ). Is there a way to configure this Flat File method to break the "contacts" into separate rows and carry over the first four columns as well without having to manipulate the files?

Ravi15 Newbie Points: 1 More actions · Answer 2

Hello

Can you please provide some description on how to parameterise the json files and how to loop through the package and load multiple Json Files in all the kinds Please

Thanks

uBrix Newbie Points: 1 More actions · Answer 3

Your posts have helped e a lot, however how do I handle a situation in which the json files change names, such as say they have a daily date stamp. Is there a way to parametize the query if we go with the openrowset option?

Michael Lysons SSCertifiable Points: 6492 More actions · Answer 4

One solution we have used in the past is to rename the file to a standard name as part of the process, e.g. MyDailyFile_2020_03_20.json would be moved to a new location where the actual load will take place, and in the process of the move it will be renamed to MyDailyFile.json.