SSIS package execution succeeds but doesn't do its job

Question

I use SQL Server Agent to fill tables in DataWarehouse. I have about 50 steps in the job and every step is run with a proxy account. And every step works correctly besides one:

SSIS package which contains about 20 Execute SQL Tasks which execute procedure. This is what I have in the Execute SQL Task:

DECLARE @RC int

DECLARE @fordate datetime = null

DECLARE @tablename sysname = 'D_ENTITY'

DECLARE @dataFolder varchar(1024) = 'C:\MountPoints1\src_etl\'

DECLARE @logFolder varchar(1024) = 'C:\MountPoints1\src_etl\'

DECLARE @debug bit = 0

EXECUTE @RC = [dbo].[ETL1A_HR] 
 @fordate
,@tablename
,@dataFolder
,@logFolder
,@debug
GO

The thing is, that if I execute the package from the SSIS catalog, it works ok. But if it is run by job, it succeeds, but only deletes from tables, but doesn't fill it. It seems like the procedure stops somewhere in the middle.

Any ideas? Please advise, it took me days trying to solve this...

Answer 1

I think it maybe related to permissions. Executing the SSIS package will use your security context but running it from the agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.