If you encounter the following error:
Could not create DTS.Application because of error 0x80040154
From calling DTEXEC.exe. For instance like so:
& DTEXEC.exe /FILE $SSISLocationPackageLocationIt could well be the case that the DTEXEC that is being called is not compatible with the database server you want to run the SSIS package on. Particularly your development environment may be susceptible to this problem.
To solve this, search for all the different DTEXEC.exe versions on your machine and find the one that is compatible with the SQL Server version you are running the package on. Then run DTEXEC with the explicit DTEXEC.exe location like this:
& "C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\CommonExtensions\Microsoft\SSIS\160\Binn\DTEXEC.exe" /FILE $SSISLocationPackageLocation