I have a SSIS package, developed for SQL Server 2012, that uses a script component to open an Excel workbook and execute a macro. This package runs in visual studio, and I am able to deploy to SQL Server 2012.
In SQL Server, I am able to execute the package by selecting it from the Integration Services Catalogs and Right Click --> Execute... (with 32-bit runtime). I understand that this executes the package as the user that I am logged in to the server as. The package executes successfully in this scenario.
When I create a SQL Server Agent job to execute the package nightly, I have created a proxy user with the same domain user that I used to log into the SQL server it fails with the following error:
The Execute method on the task returned error code 0x80070002 (Could not load file or assembly 'Microsoft.VisualStudio.Tools.Applications, Version=188.8.131.52, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.). The Execute method must succeed, and indicate the result using an "out" parameter.
I have ensured that the SQL Server Agent job is also using the 32-bit runtime. Since the proxy user is also the user that I logged into the SQL Server with, I do not believe it is a permissions issue (at least for that user).
Does SQL Server Agent have other permissions that need to be granted?
Try giving read/write permissions to the SQL Database Engine Service account
NT SERVICE\MSSQL$<Instance Name> (Where
<Instance Name> should be replaced by the installed instance name):
User contributions licensed under CC BY-SA 3.0