Trigger store procedure with a file in SQL Server

• IMary

  Old Hand

  Points: 361

  More actions

  November 18, 2019 at 9:35 pm

  #3698655

  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 4 years, 5 months ago by  IMary.

• Lowell

  SSC Guru

  Points: 323504

  More actions

  November 18, 2019 at 10:18 pm

  #3698658

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

  More actions

  November 19, 2019 at 2:39 pm

  #3698835

  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.
  

  ---

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