SQL Server Integration Package fails with non-descript error. Works in VS2019 but not from commandline using DTEXEC

Question

I'm having issues running a .dtsx package made in Visual Studio 2019. In Visual Studio when using "Start without debugging" it runs perfectly with no errors, however when I try to launch the file from command line it's giving me a non-descript error and terminates the package. It runs for some time and does execute a few of the tasks at the start of the package, however it does not get very far.

This is the "error" showing in the command prompt: Message: <DTS:ProcessMemoryUsage xmlns:DTS="www.microsoft.com/SqlServer/Dts">DTS:PeakWorkingSetSize177860608</DTS:PeakWorkingSetSize></DTS:ProcessMemoryUsage>

When I look at the error dump, this is what I see:

This is my execution command

"C:\Program Files\Microsoft SQL Server\130\DTS\Binn\DTExec.exe" /f "C:\**PATH**\Visual Studio 2019\Projects\DataDropPrep\DataDropPrep\MSX DataDrop.dtsx" /conf "C:\**PATH**\Visual Studio 2019\Projects\DataDropPrep\Test.dtsConfig" /DECRYPT "****" /CONSOLELOG /DumpOnError /REPORTING EW

I don't understand why it works perfectly when run within Visual Studio 2019 but not in the commandline. The package is set with target server 2016 (To match our SQL server).

I'm running the package from a local VM on my own PC, but the SQL server is deployed in Azure (cloudapp.azure.com). I am NOT a DB admin, so I can't add the package to be run from the SQL Server itself unfortunately, wish that I could, would make my life a lot easier. Alas I am stuck manually running this package every day from my own PC, and I'm trying to automate it with a .bat file, so I need this command working.

This is the package layout, it first truncates a staging table, then loads all items modified or created in the past 3 days, then deletes matching rows in the production table, then imports the new rows from staging. Repeat for all the different objects:

When using "Start without debugging" from Visual Studio 2019, it executes without issues:

Answer 1

Turns out the issue was that I was using a project parameter, which works when running from Visual Studio, but not when executing the dtsx file directly. After converting the parameters from project to package, it executes successfully!

Without the logging and the insights that provided I wouldn't have known where to start.

Success

Answer 2

In the same folder as the package, SSIS generated a file SQLDUMPER_ERRORLOG.log. This pointed to a file in C:\Program Files\Microsoft SQL Server\150\Shared\ErrorDumps. This folder contains text files such as SQLDmpr0085.mdmp. When I click on this, it launches Visual Studio and gives an error message "The thread tried to read from or write to a virtual address for which it does not have the appropriate access."

After hours of experimenting and doing web searches, I discovered that I was not passing the correct package name to dtexec.exe. Once I corrected this, I was able to execute packages with no issues.

Answer 3

How they are read aloud depends on the screen reader being used. VoiceOver reads it as one phrase, but that doesn't mean others will. Having it split up wouldn't be a nice experience, but it doesn't mean it's not accessible.

If you really want to make sure it's read as one phrase but don't like the noise of the template literal inside the JSX (I agree), why not define the string somewhere else until you are able to test on multiple screen readers?

const cloudinessSummary = `${cloudiness}% (${cloudinessDescription})`;

return <p>{cloudinessSummary}</p>;