SSIS Package to read data from JSON file

isteyak ahmad 1 Reputation point
2021-08-24T11:09:35.72+00:00

Hi Team,

I have a requirement where I need to read the JSON file which is getting created at shared location in every few second, and load the JSON data in database.

I wanted to understand if I can create a SSIS package which can be schedule to execute every minute to extract the JSON file data and load into database.

Thanks,
Isteyak Ahmad

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,641 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. COZYROC 101 Reputation points
    2021-08-24T11:30:17.997+00:00

    Starting with SQL Server 2016, you can read JSON directly from T-SQL using the OPENJSON function. For further information, check the post here.

    1 person found this answer helpful.
    0 comments No comments

  2. ZoeHui-MSFT 40,356 Reputation points
    2021-08-25T01:40:16.287+00:00

    Hi @isteyak ahmad ,

    We may use script component to meet the requirement in SSIS.

    Step by step you may refer below.

    importing-json-files-using-sql-server-integration-services

    If you want schedule the package, you may scheduling Jobs in SQL Server Agent.

    sql-server-agent-jobs-for-packages

    Regards,

    Zoe


    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.
    Hot issues October


  3. Daniel Calbimonte 1 Reputation point
    2022-06-22T20:50:26.587+00:00

    There are several third-party tools to connect to JSON files like ZappySys, and Cozyroc.
    You only need to download the software and connect to your json file with those third-party components.

    To schedule, you can use the SQL Agent to run the jobs.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.