Error during loading of SSIS package

Question

Hi,

I'm trying to execute a SSIS package on a SQL Server 2008R2. The script retrieve data on a remote server and copy them to its local database.

This job is scheduled every hour, the SQL Agent use a proxy to authenticate itself to the remote machine. Authentication seems to be ok but I get an error during loading of the SSIS package.

This is what I get:

Could not load package  because of error 0xC001404A. Description: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_ssis_getfolder', database 'msdb', schema 'dbo'.).

When I add the role of sysadmin to this user, the script works well. I don't know which kind of right I have to add to user on database 'mdb' to avoid this error.

Anybody could help me, please ?

Thanks in advance

Answer 1

You could GRANT EXECUTE ON MSDB.DBO.sp_ssis_getfolder to [proxy]

Or you could try adding that user to one of the dts/ssis operator/executor roles - starting with user, then operator, then admin to see what level it needs - or read the documentation.

db_ssisadmin db_ssisoperator db_ssisltduser

See the section under msdb here: http://msdn.microsoft.com/en-us/library/ms189121.aspx