Gotcha in Executing SSIS from DTExec Command Line

This issue was found in a troubleshooting session in one batch job with one company. The company runs its SSIS using a third party enterprise software by calling dtexec command prompt utility (dtexec.exe) and passes the arguments from its interface.

The enterprise suite calls the SSIS package using an Active Directory account (DOMAIN\BatchJob) and the SSIS package is comprised of a *.dtsx and *.dtsConfig files (using legacy deployment model).

The *.dtsConfig contains the following:

<?xml version="1.0"?>
<DTSConfiguration>
  <DTSConfigurationHeading>
    <DTSConfigurationFileInfo GeneratedBy="DOMAIN\OriginalCreatorNTID" GeneratedFromPackageName="DTS_Name" GeneratedFromPackageID="{GUID}" GeneratedDate="3/31/2021 10:00:00 AM"/>
  </DTSConfigurationHeading>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[CM_ADO_MyDB].Properties[ConnectionString]" ValueType="String">
    <ConfiguredValue>Data Source=DB-SERVER;Initial Catalog=ITNotaDB;Integrated Security=True;</ConfiguredValue>
  </Configuration>
  ...

Without revealing much of the enterprise application suite (since it’s irrelevant), the application has a box command to run to call on the dtexec.exe with another box to pass in arguments such as the following:

/FILE "D:\Temp\ITNotaSSISJob.dtsx" /CONNECTION "CM_ADO_MyDB";"Data Source=DB-SERVER;User ID=MyNTID;Password=*****;Initial Catalog=ITNotaDB;Application Name=SSIS-DTS_Name-{GUID}CM_ADO_MyDB;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

This can be easily replicated in the Command Prompt by typing the following:

C:\>"D:\Program Files (x86)\Microsoft SQL Server\###\DTS\Binn\dtexec.exe" /FILE "D:\Temp\ITNotaSSISJob.dtsx" /CONNECTION "CM_ADO_MyDB";"Data Source=DB-SERVER;User ID=MyNTID;Password=*****;Initial Catalog=ITNotaDB;Application Name=SSIS-DTS_Name-{GUID}CM_ADO_MyDB;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

What happened then the job could not run because of the following error:

Error: 2021-03-31 11:20:32.54
Code: 0xC0208449
Source: DFT_***** [19]
Description: ADO NET Source has failed to acquire the connection {GUID} with the following error message: "Cannot open database "ITNotaDB" requested by the login. The login failed.
Login failed for user 'DOMAIN\BatchJob'.".
End Error

This is interesting because the assumption was that the user id that was used to login to the database would be MyNTID, which was passed as an argument. But what was really happening is that the service account NTID used to run the dtexec (whether it’s being called from a third party of the command prompt) is the one used as the database login. So what happened?

When you look at the two configuration settings, the assumption is during runtime, the argument that is passed from the command line would be the one that takes precedence over the configuration in the *.dtsConfig file. It turned out that is not the case. The setting from *.dtsConfig file is the one that takes precedence.

For a complete explanation, please read Understanding How Package Configurations Are Applied at Run Time. The most important part is quoted below:

The way in which the dtexec utility applies configurations affects the following command-line options:

You can use the /Connection or /Set option at run time to load package configurations from a location other than the location that you specified at design time.

You can use the /ConfigFile option to load additional configurations that you did not specify at design time.

However, these command-line options do have some restrictions:

You cannot use the /Set or the /Connection option to override single values that are also set by a configuration.

You cannot use the /ConfigFile option to load configurations that replace the configurations that you specified at design time.

So when there are configurations set in the *.dtsConfig file, it takes precedence over the argument passed from the command line.

Once we got this figured out, the client had to decide whether to remove the *.dtsConfig file or remove the injecting of parameters from the argument box (recommended) but grant an appropriate rights (e.g., dbo) to the service account (DOMAIN\BatchJob).

Further Reading

Run an SSIS package from the command prompt with DTExec.exe
Legacy Package Deployment (SSIS)
How to Create SSIS Package Using Visual Studio
How to Fix Failed to Decrypt Protected XML Node Error in SSIS