Trigger store procedure with a file in SQL Server

  • IMary

    Old Hand

    Points: 361


    Will receive a JSON file from an application on to D drive D:\tmp

    Whenever I receive that JSON file, a stored procedure has to execute  taking that JSON file as input file?

    How do I automate it?

    • This topic was modified 7 months, 2 weeks ago by  IMary.
  • Lowell

    SSC Guru

    Points: 323450

    Create an SSIS package that processes the file;

    whether the procedure calls CLR to process the file, or you load the file contents into a SQL table via that SSIS package , and then call the stored procedure is up to you.

    then you can have a job that calls that SSIS package every x minutes, across a schedule when you might expect the file.

    (ie i expect the file M-F somewhere between 4 am and 7 am, execute the package every ten minutes.)


    you use a foreach loop to go through all files int he folder, even if you expect there to be only one.

    that way, no files means the package exits quickly when no files found.



    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Jeff Moden

    SSC Guru

    Points: 996639

    You allow an app to write files  to your SQL Server?  I don't allow that.  It have people write their files to a different box and then give SQL Server the privs to see the drive(s) on that box.


    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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