How to launch cmd.exe to execute DTExec using Task Scheduler?

Question

I'm trying to automate a SSIS package using dtexec via Task Scheduler (as I do not have rights to create it via SQL Agent) and need it done locally on my machine. I referenced this post: How to launch cmd.exe to execute command using Task Scheduler ?, but I can't get the syntax right for dtexec.

My Program Script is:

"C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtexec.exe" 

with Add Argument of:

/f “C:\Users\f9a\Desktop/HRFact.dtsx”

But when it runs, it pops up the cmd window for a split second, then goes away. The History shows a return code of 4.

I can run this in cmd.exe by typing this:

dtexec/f “C:\Users\f9a\Desktop/HRFact.dtsx” 

and it works fine. I just can't seem to get the syntax for Task Scheduler. Can you help? Thanks in advance!

-Trey

Answer 1

Just change Start In textbox value to

C:\Program Files\Microsoft SQL Server\120\DTS\Binn\

And change Program/Script textbox value to

DtExec

Useful links

• https://msdn.microsoft.com/en-us/library/ms162810.aspx
• How to schedule a SSIS package job in SQL sheduler or Windows scheduler?
• http://www.microsoft-sql-ssis.com/2015/09/running-ssis-package-from-command.html
• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/eb800310-bfb4-4caf-ad8e-f3f9c36ff885/running-the-ssis-package-using-the-task-scheduler?forum=sqlintegrationservices

Answer 2

(This should probably be a comment, but is a bit long for that. Sorry.)

Your task seems to work correctly. That you only see the console window pop up quickly is because DTEXEC starts (it is a console application after all), but exits immediately with an error.

The return code 4, which is the exit code (or ERRORLEVEL) DTEXEC returns, when it cannot find the package file (according to this).

Since the package seems to be located in the directory of a particular user (“C:\Users\f9a\Desktop/HRFact.dtsx”), make sure that the user that runs the task has access to this directory.

(Also for sanity, consider using a backslash before the filename, albeit that shouldn't matter)