在服务器代理中执行的 SSIS 包不起作用(即使报告成功) [英] SSIS package executed in Server agent doesn't do its work (even while reporting success)

问题描述

我不得不说，我讨厌自己问我做错了什么?"这样的一般性问题.但我根本不知道是什么问题:

I have to say that I hate myself for such general question as "What I am doing wrong?" but I simply have no idea what can be the problem:

我创建了 SSIS 包，它从平面文件 (CSV) 中获取数据，计算其中一列的平均值，按日期分组并将其写入数据库并删除原始文件.在 SSIS 中执行时一切正常，但是当我在服务器代理中调度它时它根本不起作用 - 日志报告成功但数据库中没有新数据并且 .csv 文件存在于其原始位置.我知道在 SSIS 中设置保护级别的问题，因此我已将其更改为EncryptAllWithPassword"，并在服务器代理中使用相同的密码.这里是服务器代理作业脚本的链接(创建为脚本作业作为 DROP 和 CREATE")

I've created SSIS package that takes the data from flat files (CSV), counts the average on one of the columns, groups by date and writes it to the database and deletes the original file. All works fine when executed within SSIS, but when I am scheduling it within Server Agent it simply doesn't work - log reports success but there is no new data in the database and the .csv file exists in its original location. I know the problem with protection level set up in SSIS, so I've changed it to "EncryptAllWithPassword" and I use the same password with Server Agent. Here is a link to the Server Agent Job script (created as "script job as DROP and CREATE")

只是为了让事情变得更奇怪，使用

Just to make things weirder, using

  dtexcec /f {filepath} /de {password} 

执行程序没有问题.我知道我可以在 Windows 本身中发布这样的命令，但我想将所有计划的作业保存在一个地方 - 在服务器代理中

executes program without problem. I know I can shedule such command in the Windows itself, but i'd like to keep all scheduled jobs in one place - in the Server Agent

通过将路径更改为 UNC 解决

Solved by changing the path to UNC

推荐答案

在设置包以通过 SQL Server 代理作业运行时，需要记住两件重要的事情.

There are two important things to remember when setting up packages to run via a SQL Server Agent job.

对所有文件位置都使用 UNC 路径，无论多么简单.服务器的文件结构视图很可能与您的开发机器不同，因此 UNC 路径确保两台机器都引用相同的路径.

Use UNC paths for all file locations, no matter how simple. There is a high probability that the server will have a different view of the file structure than your development machine, so UNC paths ensure that both machines are referencing the same paths.

使用代理帐户执行该包，如下所述 http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-帐户/.代理帐户必须有权访问物理路径和服务器对象.这还允许对您的各种包进行安全分层(并非所有包都需要访问所有内容).

Use a proxy account to execute that package, as described here http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/. The proxy account must have access to the physical paths and the server objects. This also allows for security stratification on your various packages (not all packages need access to everything).

这篇关于在服务器代理中执行的 SSIS 包不起作用(即使报告成功)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！