SSIS Package Runs from Catalog, not from SQL Server Agent (Same User)

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?

sql-server
ssis
etl
sql-server-agent
asked on Stack Overflow Jul 24, 2019 by user7656032 • edited Jul 24, 2019 by Hadi

2 Answers

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

answered on Stack Overflow Jul 24, 2019 by Hadi
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.

answered on Stack Overflow Jul 24, 2019 by user7656032

User contributions licensed under CC BY-SA 3.0