Trigger store procedure with a file in SQL Server

  • IMary

    Old Hand

    Points: 356

    Hi,

    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 3 weeks, 3 days ago by  IMary.
  • Lowell

    SSC Guru

    Points: 323442

    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.

     

    Lowell


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

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code 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