I created a very basic SSIS package which reads data from local sql server and outputs to a file the results. The package is read as a file package.
I have SQL 2005 9.0.500 on Windows Server 2008 R2 6.1.7601. This is a cluster.
If I change the Run as to a proxy account (domain user) this fails, but if I change to the SQL Agent Service Account, it runs successfully.
I granted permission to the folder to the proxy's credential where the output is going and also where the package is being read from (same folder). I even granted admin permission to the server itself for the proxy's credential. Still, no luck.
I also tried creating the credential as SQL logins and granted it sysadmin privileges. Again, no luck.
What am I missing?
Thanks in advance.
My idea the proxy account does not have the access to the database.
If you tell us the opposite please provide the error message.
Arthur My Blog
This could be caused by the protectionlevel setting of your package. As we know, by default, the protectionlevel is set to "EncryptSensitiveWithUserKey" which means that the sensitive data will be enrypted by the current user profile, other users whoever windows admin or SQL sysadmin can't open or run the package. So whatever you grant any permission on the proxy account, who still failed to run the package.
To solve the issue, change the package protectionlevel to EncryptSensitiveWithPassword, then you need to supply the password each you run the package.
See http://msdn.microsoft.com/en-us/library/ms141747.aspx to learn more about Setting the Protection Level of Packages.