CODEWITHSUNDEEP
excelssissql-server-2012
Echo
Echo

Reputation: 1137

SSIS package works from SSMS but not from agent job

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.

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

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.

The same thing also happened to "SSIS job" instead of T-SQL job, even with proxy of windows 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

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

Upvotes: 4

Views: 1896

Answers (4)

Gordon Bell
Gordon Bell

Reputation: 13633

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.

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

Upvotes: 1

sandeep rawat
sandeep rawat

Reputation: 4957

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

 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 ..

Upvotes: 0

SinisterPenguin
SinisterPenguin

Reputation: 1618

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.

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?

Upvotes: 0

Mike Honey
Mike Honey

Reputation: 15037

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.

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

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

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

Upvotes: 0

Related Questions