Multiple SSIS Package Execution by Agent: one step fails with "must install standard edition of Integration Services"

Question

We have seven SSIS packages that are stored as File System packages on server A, which is running Integration Services 15.0.2000. All of the packages use configuration files and all of them run successfully when run individually on Server A (by connecting to Server A integration services using SSMS).

Each package does essentially the same task: run a stored procedure, export the data to a text file on a network share.

We are trying to execute the Server A packages using a SQL Server Agent Job on Server B (SQL Server 15.0.2000.5). The job has seven steps: one for each package. Each step has the identical setup. The only difference is the package name and the configuration file.

When the Agent Job is run, six of the seven steps run successfully. One fails, returning this error:

Code: 0xC000F427 Source: Data Flow Task SSIS.Pipeline Description: To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher.

This is counterintuitive since the other six packages already ran successfully.

The packages all share the same package types, package format versions.

I've created a new version of one of the failing packages from scratch and it still fails.

Not sure where to go from here.

Answer 1

While trying to document the problem thoroughly, I came across a fix.

The package that was failing is the only one of seven that had a Derived Column object in the data flow. I moved the derived column into the stored procedure, removed the Derived Column object, mapped the column to the flat file destination, redeployed to Server A, and voila. It worked.

I don't know why the Derived Column object was an issue. Again, the package ran fine from the SSIS server itself. The problem presented itself only when attempting to execute the package from a different server using a SQL Server Agent.