SQL Agent and Windows Task Scheduler

  • Hi Guys,

    Has anyone used windows task scheduler with SQL Agent? I have a FTP which needs to be imported into a database every morning.

    At the moment we save the FTP as a CSV and currently import that into the database but I'm hoping to fully automate the importing process?

    I'm hoping widows task scheduler can open ssms and run sql agent for me.

     

    Thanks

    • This topic was modified 4 years, 1 month ago by  Zed.
  • I think a wmi event would work. A sql server agent wmi alert when a file is downloaded and then calls a sql server agent job.

     

  • I don't think you could have task scheduler open up SSMS and execute a job through SSMS but you really don't necessarily need to do it that way. You could use Windows task scheduler to run sqlcmd and that could execute sp_start_job to start a job.

    Sue

  • There is one blog post with instructions on wmi alert

    https://www.sqlshack.com/create-configure-sql-server-agent-alerts/

     

  • How are you importing the CSV file into the database?  What is the agent job doing - is it just running T-SQL code or is it executing something else?

    For something like this - I would probably use SSIS where I could then download the file and load it in one package.  If SSIS wasn't an option - I would then use a Powershell script to download the file from the FTP site and then use BCP command line to load the file.

    Once the script is created, it could then be scheduled using either task scheduler or the agent (my preference).

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I have a job that checks for the existence of files on the intake folder, then evaluates the file name mask against an ETL catalog. If the file matches, the process invokes the Agent job that fires the related SSIS package.

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

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