SQL Server Agent successfully executes one package but fails with another generating error: "To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher. "

Question

My organization currently has a SQL Server Agent job which executes an SSIS package to move data from their Production environment to a BI / Reporting environment.

I need to bring some additional columns over and they would like me to use a separate SSIS package, executed as an additional step within the current SQL Server Agent job.

They are running SQL Server 13.0.5492.2. Their existing SSIS package is executed from the file system (not deployed).

When I attempt to execute my new SSIS package through the same SQL Server Agent job I receive the error: "To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher." My failing package is saved in the same directory as the working one.

My question: what could be causing the above error message while a different package executed successfully?

I have tried the following:

1. Creating package in VS 2019 and 2017 and targeting SQL Server 2019 and 2017. The package executes successfully in VS with both of these configurations. In both versions of VS attempting to target SQL Server 2016 causes the package to cancel execution immediately after the validation phase while in debug mode (possibly a separate issue?). In all instances the above error message appears when attempting to execute the package outside VS.
2. Creating package in VS 2013 and 2012 (the creator of the working package thought it was created in one of these versions). Again, works in VS but not when executed by SQL Server Agent.
3. Converting to package deployment model and deploying to the file system. The dtsinstall.exe fails with no error message. There is an error log file created but I don't have sufficient permissions to view it.
4. Deploy to integration services catalog. Creation of initial catalog fails because it can't find the file SSISDB.bak

Thanks in advance for any help.

Answer 1

you must install Standard Edition (64-bit) of Integration Services or higher.

I think it's a pretty clear message.
Is SSIS installed on the machine? Run the Discovery Report to validate it.
https://learn.microsoft.com/en-us/sql/database-engine/install-windows/validate-a-sql-server-installation?view=sql-server-ver15

Answer 2

Hi @Christopher Prosser ,

To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher.
Deploy to integration services catalog. Creation of initial catalog fails because it can't find the file SSISDB.bak

I think that the resolution is that you have to install the Integration Services (Shared feature) from the SQL Server Installation which is necessary to execute .dtsx packages outside of visual studio on your machine.

Please refer to install-integration-services.

In addition, please make sure the TargetServerVersion matches with your SQL Server.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.