SSIS包从SSMS工作，而不是从代理工作

I've an SSIS package to load excel file from network drive. It's designed to load content and then move the file to archived folder.

我有一个从网络驱动器加载excel文件的SSIS包。它被设计为加载内容，然后将文件移动到归档文件夹。

Everything works good when the following SQL statement runs in SSMS window.

当以下SQL语句在SSMS窗口中运行时，一切都运行良好。

However when it's copied to SQL agent job and executes from there, the file is neither loaded nor moved. But it shows "successful" from the agent log.

但是，当它被复制到SQL代理任务并从那里执行时，文件既没有被加载也没有被移动。但是它显示了代理日志中的“成功”。

The same thing also happened to "SSIS job" instead of T-SQL job, even with proxy of windows account.(same account as ssms login)

同样的情况也发生在“SSIS job”中，而不是T-SQL作业，即使是windows帐户的代理。(与ssms登录账号相同)

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'SG_Excel.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'ETL', @project_name=N'Report', @use32bitruntime=True, @reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

P.S. At first relative path of network drive is applied, then switched to absolute path(\\server\folder). It's not solving the issue.

首先应用网络驱动器的相对路径，然后切换到绝对路径(\服务器\文件夹)。这并不能解决问题。

4 个解决方案

#1

SSIS Package Jobs run under the context of the SQL Server Agent. What Account is setup to run the SQL Server Agent on the SQL Server? It may need to be run as a Domain account that has access to the network share.

SSIS包作业在SQL Server代理的上下文中运行。在SQL服务器上运行SQL Server代理的设置是什么?它可能需要作为访问网络共享的域帐户运行。

Or you can copy the Excel file to local folder on the SQL Server, so the Package can access the file there.

或者您可以将Excel文件复制到SQL服务器上的本地文件夹中，这样包就可以访问那里的文件。

#2

Personally I avoid the File System Task - I have found it unreliable. I would replace that with a Script Task, and use .NET methods from the System.IO namespace e.g. File.Move. These are way more reliable and have mature error handling.

我个人避免文件系统任务——我发现它不可靠。我将用脚本任务替换它，并使用系统中的。net方法。IO命名空间例如File.Move。这些方法更可靠，并且有成熟的错误处理。

Here's a starting point for the System.IO namespace:

这是系统的起点。IO命名空间:

https://msdn.microsoft.com/en-us/library/ms404278.aspx

Be sure to select the relevant .NET version using the Other Versions link.

确保使用其他版本链接选择相关的。net版本。

#3

When I have seen things like this in the past it's been that my package isn't accessing the path I thought it was at run time, its looking somewhere else, finding an empty folder & exiting with success.

当我过去看到类似的事情时，我的包并没有访问我认为它在运行时访问的路径，而是在寻找其他地方，找到一个空文件夹并成功退出。

SSIS can have a nasty habit of going back to variable defaults . It may be looking at a different path you used in dev? Maybe hard code all path values as a test? or put in break points & double check the run time values of all variables & parameters.

SSIS有一个坏习惯，就是回到可变的默认值。它可能正在考虑您在dev中使用的不同路径?也许所有路径值的硬编码都可以作为测试?或者输入断点，再次检查所有变量和参数的运行时间值。

Other long shots may be:

其他的长镜头可能是:

Name resolution, are you sure the network name is resolving correctly at runtime?

名称解析，您确定网络名称在运行时正确解析吗?
32/64 bit issues. Dev tends to run 32 bit, live may be 64 bit. May interfere with file paths? Maybe force to 32 bit at run time?

32/64位问题。Dev趋向于运行32位，live可能是64位。可能干扰文件路径?可能在运行时强制到32位?

#4

There is issue with sql statement not having statement terminator (;) that is causing issue.

sql语句没有语句终止符(;)会导致问题。

 Declare @execution_id bigint ;
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'SG_Excel.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'ETL', @project_name=N'Report', @use32bitruntime=True, @reference_id=Null ;
Select @execution_id ;
DECLARE @var0 smallint = 1 ;
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0 ;
EXEC [SSISDB].[catalog].[start_execution] @execution_id ;
GO

I have faced similar issue in service broker ..

我在服务中介公司也遇到过类似的问题。

#1