Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent
It sounds like your connection manager is setup to use windows Authentication.  If that is the case, when you execute the package via the SQL Server Agent it will run with credentials of the agent.  

You need to do one of three things:


Give the account that the SQL Server Agent runs as appropriate permissions to execute SPs in your database.
Setup a proxy account for the agent with the appropriate permissions (as suggested by Jon Seigel).
Use a different authentication method - i.e SQL Server Auth.


I would go with option 1 if possible, but it depends what your company's policies are.