Remote execution of DTExec.exe produces error 0x80040154

Question

I created a very simple SSIS package in VS2019. It contains only a SQL script step which sends an email through dbdmail, so I will know it is successful.
I then created a batch file (on the same server) to call the SSIS package (SQL2019). The contents are:

    :: Set File paths
    SET "LogPath=D:\LOGS\SSIS_TEST_AS1.log"
    SET "DtsxPath=D:\SSIS\Packages\SSIS_TEST_AS1.dtsx"

    "D:\Program Files\Microsoft SQL Server\150\DTS\Binn\DTExec.exe" /FILE %DtsxPath%  /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EP >> %LogPath%
    if ERRORLEVEL == 0 SET ERRORLEV=0
    if ERRORLEVEL == 1 SET ERRORLEV=1
    echo ERRORLEVEL = %ERRORLEV%
    exit /B %ERRORLEV%
    echo PS Stage Complete

This is successful.

Now, I have been told we will need to place batch files on a separate server due to security issues.
I created a share (named Share1 which starts at D:\Program Files\Microsoft SQL Server) for SQL Server and granted permissions.

My new script is:

    :: Set File paths
    SET "LogPath=\\RemoteServer\Logs\SSIS_TEST_AS1.log"
    SET "DtsxPath=\\RemoteServer\SSIS\Packages\SSIS_TEST_AS1.dtsx"

    "\\RemoteServer\Share1\150\DTS\Binn\DTExec.exe" /FILE %DtsxPath%  /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V >> %LogPath%
    if ERRORLEVEL == 0 SET ERRORLEV=0
    if ERRORLEVEL == 1 SET ERRORLEV=1
    echo ERRORLEVEL = %ERRORLEV%
    exit /B %ERRORLEV%
    echo PS Stage Complete

This fails with the following error: Could not create DTS.Application because of error 0x80040154
I am able to call DTEXEC from the 2nd server. Running "\RemoteServer\Share1\150\DTS\Binn\DTExec.exe" returns: "At least one of the DTS, SQL, ISServer or File options must be specified."

Does anyone know why executing the package won't work??

Answer 1

The error message "Could not create DTS.Application because of error 0x80040154" indicates that the COM object for SSIS is not registered on the remote server. This can occur if the SQL Server Integration Services (SSIS) feature is not installed on the remote server, or if the version of the SSIS feature on the remote server is different than the version used to create the SSIS package.

To resolve the issue, you can try one or more of the following:

1. Install the SSIS feature on the remote server: Make sure that the version of SSIS feature on the remote server is compatible with the version used to create the SSIS package. You can install the SSIS feature using SQL Server Setup or by using PowerShell.
2. Register the SSIS COM object manually: If the SSIS feature is already installed on the remote server, but the COM object is not registered, you can try to register it manually. To register the SSIS COM object, you can run the following command from an elevated command prompt: regsvr32 "C:\Program Files\Microsoft SQL Server<version>\DTS\Binn\DTS.dll" (replace <version> with the version of SQL Server installed on the remote server)
3. Use a different method to execute the SSIS package: Instead of using the DTExec command-line utility, you can try using other methods to execute the SSIS package remotely, such as SQL Server Agent or PowerShell. You can also try using a third-party scheduling tool to execute the SSIS package.