I am trying to run a number of SQL Server 2008 stored procedures that are wrapped in SSIS packages consisting mainly of Execute SQL Tasks. They run fine in Visual Studio 2008 after deploying to a folder on the SQL Server server.
The problem is when I created a job in SQL Server Agent and tried to run the job. That is when I get the error:
Executing the query "EXEC ? = dbo.usp_Clear_All_JDAWG_Tables" failed with the following error:
"The EXECUTE permission was denied on the object 'usp_Clear_All_JDAWG_Tables' database 'JDAWGD' schema 'dbo'.".
Possible failure reasons:
Problems with the query "ResultSet" property not set correctly
parameters not set correctly
or connection not established correctly.
DTExec: The package execution returned DTSER_FAILURE (1).
usp_Clear_All_JDAWG_Tables is the name of the first of many packages this job is trying to run. The stored procs themselves do not use parameters but in the SQL Task I have commands like this to run the stored proc:
EXEC ? = dbo.usp_Load_JDAWG_Tables_From_Persons_All_Agencies
Is there a better/different way to run a stored procedure in the SQL Task?
As a side note, I was able to create and successfully run an Agent job that directly executed the stored procedures that the packages run.