0

I'm migrating all my SSIS packages and solution to Visual Studio 2022. When I try the packages inside visual studio they run ok without problems. But when I try it from cmd and I get this error:

Microsoft (R) SQL Server Execute Package Utility
Version 16.0.5131.0 for 64-bit
Copyright (C) 2022 Microsoft. All rights reserved.

Started:  14:59:06
Could not create DTS.Application because of error 0x80040154
Started:  14:59:06
Finished: 14:59:06
Elapsed:  0.016 seconds

The following is a sample of how I am running the package.

dtexec.exe /f "c:\Clockify\Clockify.dtsx"

I try to find any solution on internet, but I didn´t find anything. I updated to the latest version of Visual Studio 2022 and the SSIS package.

billinkc
  • 59,250
  • 9
  • 102
  • 159
Pablo Costa
  • 1
  • I would expect another line of error information, but generally speaking, if a package runs from VS but not the command line, either your command is bad or more likely, you do not have the SQL Server Integration Services Service installed. Among other things, that service provides a licensing check. To run packages outside of the development environment, a developer/standard/enterprise edition is required. Something like https://stackoverflow.com/questions/53731230/ssis-error-to-run-a-ssis-package-outside-of-sql-server-data-tools-you-must-inst – billinkc Feb 27 '23 at 23:44
  • i don't have a SQL instance installed locally, i'm running remotely. If i run the 32 bit version of dtexec, the package runs ok. the problem is when i run the 64 bit version of dtexec. the 32 bit package was compiled on VS2019. when i run dtexec on cmd the error is all i paste on the first post – Pablo Costa Feb 28 '23 at 14:33
  • Can you help me understand that comment? Your problem statement indicates you are running the package(s) from the command line. I *assume* that means you are using `dtexec.exe` to run from command line. Perhaps it'd be more clear, if you edit your question to show the specific commands you are using? – billinkc Feb 28 '23 at 14:35
  • In the title i put that i'm having problems running dtexec from command line. The command that i'm using is: dtexec.exe /f "c:\Clockify\Clockify.dtsx" . if i run with the dtsx version compiled in Visual Studio 2019 on a 32 bit version of dtexec it runs ok. when i run the dtsx version compiled in Visual studio 2022 on the 64 bit version of dtexec provided with Visual Studio 2022 i have the error message that i copy on the first post. – Pablo Costa Mar 01 '23 at 17:13
  • Excellent, the specifics of the command are what I was looking for. You use the Package Deployment model. You changed two things from your working 2019 solution: Version and your "bittedness". Does the Clockify package run in 32 bit mode on 2022? Does the Clockify package run in 64 bit mode on 2019? Does the Clockify package run at all within the context of Visual Studio 2022? – billinkc Mar 02 '23 at 00:19
  • in 32 bit version i don't try to run the package, but there's a reason for that. in the Visual Studio 2019 version of the package the input file was a xls file, the reason for that is because i have windows and office in 64 bit and Visual Studio 2019 in 32 bit doesn't run xlsx files . Now that Visual Studio 2022 is in 64 bit i'm starting the migration of all my packages for import xlsx files . thats why my problem. on others machines that the package runs, runs the old version with the xls files – Pablo Costa Mar 03 '23 at 17:26

1 Answers1

0

base on what you've given in your Environments and intent there are still a bit of pieces missing that could be in play and further affects your development plan. first off in regards to the Error code

according to an answer in MS Q&A 1 month after your posting time, and i quote:

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.

aside from a likelihood of missing COM assemblies like Microsoft.Office.Excel.Interops , since you did not mentioned what exactly your DTSX are setup to run and do what, below are all considered and assumed in your case:

  • by meaning of remote executing, you're remote desktop connected to a VM machine and are operating locally in said remote VM.
  • dtsx involves a Script Task that do open excel workbooks for modifying contents (e.g. removing first rows of data after header row)
  • dtsx had both Excel Source and Destination involved
  • no variables involvement with the parameters, filename etc.
  • RDC Machines that executes the dtsx had side by side installation of VS2017/19 (w/ SSIS ,SSDT), and VS2022 (w/SSIS,SSDT)
  • RDC Machines was installed with a 32bit Microsoft Access Database Engine only,
  • 64bit version Microsoft Access Database Engine was not installed(or installed using cmd /slient workaround)

as in similar scenarios with you,first thing came off my mind were that the excel connection manager was using old Jet4.0 or 32bit version ACE12.0 engine, which, since VS2022 only able to execute with 64bit assemblies, your DFT would have a big fat red cross over it on the Excel source/destination complaining no 64bit ACE engine not found and recommend you run the DTSX in 32bit mode, and when debug run in VS, it did fails with error complaining pretty much the same thing.

2nd thing ,of your runtime results pasted in question , it is clear the Command was pointing the 64bit version of DTExec.exe to run the dtsx,but as aforementioned, it is unclear whether your local DTSX were fully adjusted that all parts are 64bit compliant.

it would benefits you test run the dtsx with 32bit DTexec.exe via CMD as well.

although sounds redundant since your file was from VS2017 created project and would have worked, try run with /Reporting V or /Reporting E options with both 32/64bit DTEXEC and compare the outputs to pinpoint your exact issue on your case . (if you only ever wanted the cause of death E would probably be enough, though verbose is excessive in information but may tells you which exact tasks of the dtsx was the bit it failed)

ArthurS991
  • 26
  • 4
  • missed the Ref. link , for the 64bit compliant bit, [ref](https://stackoverflow.com/a/9116133/20413443) here on another SO regarding error loading SSIS packages using 32-bit dtexec on a x64 machine – ArthurS991 Aug 17 '23 at 07:29