SQL Agent and Windows Task Scheduler

Question

SQL Agent and Windows Task Scheduler

Zed

Old Hand

Points: 337
More actions
October 15, 2020 at 1:43 pm

#3797613
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 3 years, 6 months ago by Zed.

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

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

Cebisa SSC Enthusiast Points: 151 More actions · Answer 1

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.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

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

Cebisa SSC Enthusiast Points: 151 More actions · Answer 3

There is one blog post with instructions on wmi alert

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

Jeffrey Williams SSC Guru Points: 90245 More actions · Answer 4

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

phegedusich SSCommitted Points: 1552 More actions · Answer 5

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.