October 15, 2020 at 1:43 pm
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
October 15, 2020 at 3:23 pm
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.
October 15, 2020 at 3:26 pm
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
October 15, 2020 at 5:39 pm
There is one blog post with instructions on wmi alert
https://www.sqlshack.com/create-configure-sql-server-agent-alerts/
October 15, 2020 at 7:49 pm
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
October 23, 2020 at 12:57 pm
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