SSIS Execute SQL Task Doesnt Run Through SQL Agent

0
votes

I have an SSIS package with an execute sql task that executes a stored proc that updates data in a table. When I run the package through SSDT it runs successfully, however when I run the job in SQL Agent the task executes successfully, but no data is updated in the table. In other words, the task executes successfully, but the EXEC statement doesn't actually get executed.

There are no precedent constraints or anything like that in the package. I've even disabled every other task except this one. I really think this has something to do with the proxy I'm running this package as, as when I execute the package manually through the Integration Services Catalog, it runs fine.

My agent job runs through a proxy that is mapped to a login that is set as a sysadmin server role, and is mapped to the db_owner role in every database on the server, default schema dbo.

I'm using sql server 2014.

Thoughts?

1
sql-serverssissql-server-2014
Can/have you run a profiler trace whilst the job is running to check whether the proc is called or not? – Gareth Lyons
I ran a profiler trace, granted I'm not familiar with this at all, and I see a record with textdata of "exec (my sproc)", with a runtime of 3 seconds. I'm not sure if this means the proc was executed, but again, none of the data it should be updating, was updated. I also tried to run the job with another credential which references my windows login, and the sproc ran fine. – Pops

1 Answers

0
votes

The issue was that my query is hitting filtered views in our CRM database, and the user which the SSIS job was running under was not set up as a user in CRM.