question

Ant-1880 avatar image
0 Votes"
Ant-1880 asked Monalv-msft edited

command line not running in ssis

I have an SSIS package that That contain an Execute Process Task Editor.

I've written a console app that formats and excel file.

I'm using the Executable: C:\Windows\System32\cmd.exe
Arguments: /c c:\application\file.exe 1 c:\filefolder\file.xls

the parameter 1 is a flag to tell it which path to take in the executable file.

I can run this in the IDE and when I deploy it I can run it in the Integration Services Catalogs using Excute, I set the runtime to use 32-bit runtime and it runs successfully.

When I create a job and do the same it fails.

The error in the ErrorDescription:

In Executing "C:\Windows\System32\cmd.exe" "/c c:\application\file.exe 1 c:\filefolder\file.xls" at "", The process exit code was "-532462766" while the expected was "0".

I created a batch file and the same thing happened I was able to run it in the IDE and in the Integration Services Catalogs using Execute,

I also tried to use the file.exe as an executable and it ran in the IDE and the Catalogs.

I tested it in the command window as well and it ran.

I'm at a loss does anyone have any ideas as to what I need to do to fix this. The DBA has said that the job has access and I am able to create the excel file on the server.

I'm running the version of SQL.
Microsoft SQL Server 2014 (SP2-CU17-GDR) (KB4505419) - 12.0.5659.1 (X64)
May 24 2019 19:09:40
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: )

I did not set up the server nor did the DBA we inherited it.

Please help.

Thank you.

dotnet-csharpsql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I also tested something else I created a script task in SSIS so it would be completely enclosed and just put a Messagebox.Show(“Test”); as the code. Then when I compiled it I got this error message:
Severity Code Description Project File Line Suppression State
Error Could not find a part of the path 'C:\Users\Administrator\AppData\Local\Temp\2\Vsta\b266fe211c9148099ec7360667348fe4_out'. ST_3792252086164e74bb8187d2575997f1 C:\Users\Administrator\AppData\Local\Temp\2\Vsta\SSIS_ST150\Vstad3k1nSFkAkO19MAoOGoLSg\VstalFi0DBiW7UCU4ZM78O9l0g\mscorlib 0

Several weeks ago I used a script task to debug my SSIS package and it ran. I got an update to update SSIS and now it fails. Any thoughts, I'm grasping here.

0 Votes 0 ·
APoblacion avatar image
0 Votes"
APoblacion answered APoblacion edited

Exit code "-532462766" converts to hex as E0434352 which is the exception code used internally by the CLR to represent most exceptions in .NET. So it doesn't really tell us much, except that your executable is crashing with a .NET exception.

We can't easily determine which exception it is or where it is happening. You could implement a handler for the AppDomain.UnhandledException event and use it to dump the full stack trace somewhere where you can examine it. Or you could instrument your program so that it writes to a Log everything that it's doing. This should give you an idea about where exactly it is failing and what it was doing at the time.

The most common reason why a program would run correctly when launched in one environment and fail when run from a different place is a difference in permissions. When it is running from SSIS it will be launched from whatever account SSIS is using at the time, and this may be a service account that does not have access to one of the locations that the program is trying to read or write.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Message:
Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80080005 Server execution failed (0x80080005 (CO_E_SERVER_EXEC_FAILURE)).
Stack Trace:
at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean wrapExceptions, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& hasNoDefaultCtor) at System.RuntimeType.CreateInstanceDefaultCtorSlow(Boolean publicOnly, Boolean wrapExceptions, Boolean fillCache) at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, Boolean wrapExceptions) at System.Activator.CreateInstance(Type type, Boolean nonPublic, Boolean wrapExceptions) at AccessExcel.Program.Main(String[] args) in C:\Users\Administrator\source\repos\AccessExcel\AccessExcel\Program.cs:line 48

Excel.Application ExcelApp = new Excel.Application();

Any thoughts?



0 Votes 0 ·

It is giving you a COM error when attempting to instantiate Excel through COM/Interop. There is a good reason why Microsoft disuades you from invoking Excel from a server application and tells you that it is not supported.
"Not supported" does not mean that "it can't work", but in order to make it work you need to tweak carefully the DCOM permissions. There are many articles on the 'net explaining how to do so. Most of them refer to invoking Excel from an application running under IIS, but the same principle would apply when running under SSIS, as long as the account that you configure is the service account used by SSIS instead of the IIS Pool account.

0 Votes 0 ·

Another thing to check out is the 32 versus 64 bits issue. I imagine that you have already verified this, but just in case: If your program is running in 64 bits it cannot use COM to call a 32 bit version of Excel and vice-versa. This could be affecting you if the program is compiled for "Any CPU", because then it would execute in 32 or 64 bits depending on the calling environment, and thus it could work from some places but not from others. You can edit the properties of the project and select to compile for either x86 or x64, instead of Any CPU.

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @Ant-1880 ,

I can run this in the IDE and when I deploy it I can run it in the Integration Services Catalogs using Excute, I set the runtime to use 32-bit runtime and it runs successfully.
When I create a job and do the same it fails.

Please check if you choose the 32-bit runtime in SQL Agent Job.

Could you please check if the user to execute ssis package in job is the same as the user in SSIS Catalog?
If not, please create a SQL Server Agent Proxy in job .

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

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.




· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That was the first thing I did when it failed. I also checked all the parameters. It runs fine from the SSIS catalog it only on the job scheduler that it fails.

0 Votes 0 ·

Hi @Ant-1880 ,

C:\Windows\System32\cmd.exe
Arguments: /c c:\application\file.exe 1 c:\filefolder\file.xls

May I know if you can execute the command correctly in cmd.exe?

Best regards,
Mona

0 Votes 0 ·

Yes, I copied it and pasted it directly in the cmd prompt. It ran exactly as expected. I believe it is a permission issues with the SQL Agent. I'm going to try to use the task scheduler instead and see if that works.

0 Votes 0 ·