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

  • Nikolai Basov

    SSC Enthusiast

    Points: 129

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

  • JimBuckley-RG

    Old Hand

    Points: 377

    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

    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

    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: 6478

    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.

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

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