SSIS包-通过SQL Job Agent触发时，脚本任务无法执行 [英] SSIS package - Script task does not get executed when triggered through SQL Job Agent
问题描述
我有一个使用数据流组件创建2个文本文件的SSIS包.它连接到SQL数据库，如果查询返回数据，则将创建2个文件. 之后，脚本任务循环遍历写入两个文件的文件夹，确定最旧的文件并将其移动到另一个文件夹. 当我在服务器上执行程序包时，一切都会顺利进行.没有错误，所有功能均已执行.完美的. 但是，当我在作业中计划SSIS包时，仅在SQL查询(数据流组件)未返回结果且因此未创建任何文件时才执行所述脚本任务.然后，脚本将第二个文件从上次运行移至另一个文件夹. 如果数据流确实创建了2个新文件，则脚本任务将不执行任何操作. 任何想法如何改变这种行为? 同样，这仅在通过作业执行时发生，而不是在本地运行时发生.
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.
谢谢， 丹尼尔
推荐答案
从SQL Server执行SSIS程序包时，它们使用SQL Server服务帐户NT SERVICE\MSSQL$<Instance Name> (其中<Instance Name>替换为<Instance Name>已安装的实例名称).您必须授予该帐户才能访问所选目录或使用代理帐户运行SQL作业:
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服务帐户权限:
