1

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=11.0.0.0, 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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
user7656032
  • 83
  • 5

3 Answers3

0

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):

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

It appears that the issue was with the service account attempting to open Excel. I updated the DCOM to use a specified user to open excel, based on this answer, and the job will now run via SQL Server Agent.

user7656032
  • 83
  • 5
0

I found that:

Enter "Job step properties"

There enter "Configuration" -> "Advanced"

and check "32-bit runtime"

Solved my issue.

CalgaryCoolCat
  • 11
  • 3