0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Michal Hruška
  • 444
  • 2
  • 6
  • 15
  • One note: i can't use transact sql query because it's not allowed to run on behalf of proxy account on our server – Michal Hruška Jun 10 '15 at 21:14
  • What do you mean by it only deletes but doesn't fill it?. I don't seem to find any deletes/filling going with your script. Where it reads data from in order to fill the table? Is it a file in the data folder?. – BICube Jun 10 '15 at 22:48
  • Use SQL Profiler to observe what the SP is doing. You can monitor the internal statements inside the SP as they are run. This will give you some clues as to the reason. – Nick.Mc Jun 10 '15 at 23:57
  • Hi - there are two steps in the procedure - truncate and bulk insert. And the bulk insert didn't work, because the proxy account didn't have bulkadmin permissions (solved now). But the package didn't fail, because I have a Try/Catch logic implemented, which throws error to dialog window (but it doesn't raise the dialog window when executed via agent :) ) – Michal Hruška Jun 11 '15 at 11:05

1 Answers1

0

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.

Joscion
  • 49
  • 6
  • Hi, yes that was it. I've realized this morning that the PROXY ACCOUNT didn't have PERMISSION to process BULK INSERT. I only granted permission to regular database writes and to read files from folder. Didn't realize that there are bulk admin permissions separately. – Michal Hruška Jun 11 '15 at 11:01