Question

I have a server that has a SQL Agent job setup to run a two part datawarehouse build and Analysis Services cube build. The job ran successfully until there was a problem with Management Studio and .NET framework 2.0 SP1 was re-installed. The first step in the SQL Agent job is unable to kick off the SSIS package. If the Agent job is started at step 2 it will run.

SQL agent service has been setup to use the Administrator account which is a domainh admin account and a sysadmin in the SQL Server security. The steps to troubleshoot SSIS package execution were followed from the link below.

http://msdn.microsoft.com/en-us/library/dd440760.aspx

The protection level has been set to DontSaveSensitive. The SQL Agent account has rights to run dtexec.exe and also setting up a proxy account did not resolve the issue. ZThe SSIS package will run if started manually from Management studio.

Redeploying the package and setting up the Agent job again does not resolve the issue and the package also throws the same error if run from the file system as opposed to the msdb location.

The error that the SQL Agent is throwing up is access denied by user domainnamesqlagentaccount.

Any further assistance on this issue would be greatfully appreciated.

Answer 1

Sounds like you need to poke around in dcomcnfg.

I forget the exact details but you need to set/fix some rights.

MS KB 2000474 found using a general search

Answer 2

I guess the account you use for Agent does not have enough permissions on the file system. Check the folders that were modified by the re-installation of .Net framework. If I remember correct the account should have access to the .net framework folders.

友情链接