SSIS package failure when executed in windows task schedule

Question

I have three SSIS packages to sync data from SharePoint to SQL Server, I have put them in task schedule to run daily, but one or two of them failure sometimes.

The error message from task schedule is

action "C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" with return code 2147942401"

And my task is running using a service account:

"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /F E:\SSIS\Package.dtsx /Conf E:\SSIS\config.dtsConfig

If I uncheck the Run whether user logged on or not, then all goes fine. It seems the package can't connect to the SQL Server that I config in config.dstConfig when failure. The failure also cause my db account been lock out. But why only one or two package failure, not all? And why everything goes fine if I run manually? Any help is appreciated.

The service account needs 'Log on as a service' privilege to run scheduled jobs when a user is not logged on. You can try granting the privilege and check. https://technet.microsoft.com/en-gb/library/cc794944(v=ws.10).aspx — MSBI-Geek, Feb 21 '17 at 16:08

Sven · Answer 1 · 2017-02-23T10:16:09.193

1

After much work, I found out the schedule task fail because the SSIS package validation failed before run. It's not a permission issue. But still no idea why only fail in task scheduler.

edited Feb 23 '17 at 10:16

answered Feb 23 '17 at 09:09

Sven

79
10

score 0 · Answer 2 · answered Feb 21 '17 at 21:05

You can do this workaround, it think it will solve your issue:

Instead of putting the full file path in the program/script textbox, use the Start in (Optional) field to put the folder that the dtExec file is actually in - Like so:

Check 'Run with highest privileges' Checkbox.

References

https://blogfodder.co.uk/posts/win-2008-task-scheduler-with-return-code-1-0x1/

SSIS package failure when executed in windows task schedule

2 Answers2