Search code examples
sql-serverssisetlsql-agent-jobscript-task

SSIS package - Script task does not get executed when triggered through SQL Job Agent

I have an SSIS package that creates 2 text files using a data flow component. It connects to an SQL database and if the query returns data, the 2 files are created. After that, a script task loops through the folder where the 2 files are written into, determines the oldest file and moves it to another folder. Everything moves smoothly when I execute the package on the server. No errors and all functions are executed. Perfect. However, when I schedule the SSIS package in a job, the said script task only gets executed when the SQL query (data flow component) returns no results and therefore no files are created. The script then moves the 2nd file from the last run to the other folder. If the data flow does create 2 new files, the script task does not do anything. Any ideas how to change this behavior? Again, this only happens when executed through a job, not when run locally.

Thanks, Daniel

Solution

  • When SSIS packages are executed from SQL Server they access file system using the SQL Server Service Account NT SERVICE\MSSQL$<Instance Name> (Where <Instance Name> should be replaced by the installed instance name). You have to Grant this account to access the selected directories or run the SQL job using a proxy account:

    SQL Server Service account Permissions:

    Setting proxy account: