SSIS works with SSMS, but not with agent

Question

I have an SSIS package to download an excel file from a network drive. It is designed to download content, and then move the file to the archived folder.

Everything works well when the following SQL statement is run in the SSMS window.

However, when it is copied to the SQL agent job and executed from there, the file does not load or move. But it shows "successful" from the agent’s log.

The same thing happened with the “SSIS job” instead of the T-SQL job, even with the Windows proxy account. (same account as ssms login)

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

PS First, the relative path of the network drive is applied, then it switches to the absolute path (\\ server \ folder). This is not a solution to the problem.

Answer 1

SSIS SQL Server. SQL Server SQL Server? , , .

Excel SQL Server, .

Answer 2

- . Script Task .NET- System.IO, . File.Move. .

System.IO:

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

.NET, .

Answer 3

, , , , , , , .

SSIS . , , dev? , ? .

:

• , , ?

• 32/64 bit. Dev tends to run 32 bits, and live can be 64 bits. Can file paths interfere? Maybe at run time there may be 32 bits?

Answer 4

There is a problem with the sql statement that does not have a terminator term ( ; ) causing the problem.

 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 encountered a similar problem in service brokers.