powershellssisremote-serverdtsdtexec

Empty path name is not legal - DTEXEC with Powershell

I'm trying to run a DTSX with Powershell on a remote server from my local computer, but the file path variables aren't arriving in the DTSX execution so I'm getting the error "Empty path name is not legal."

Here's an example of the command I run:

$server = "server name"
$credential = Get-Credential

$command = @'
"G:\Program Files\Microsoft SQL Server\160\DTS\Binn\DTExec.exe" /F "G:\insert-data.dtsx" ^
/SET \Package.Variables[User::file_data].Value;"\\servername\data.csv" ^
/SET \Package.Variables[User::file_log].Value;"G:\insert_data_log.txt" ^
/SET \Package.Variables[User::db_destination_server].Value;"servername" ^
/SET \Package.Variables[User::db_destination_name].Value;"dbname" ^
/SET \Package.Variables[User::db_destination_user].Value;"username" ^
/SET \Package.Variables[User::db_destination_password].Value;"pass" ^
/SET \Package.Variables[User::db_destination_table].Value;"tablename" ^
/SET \Package.Variables[User::days].Value;1
'@

Invoke-Command -ComputerName $server -Credential $credential -ScriptBlock {
    param($cmd)
    try {
        Start-Process -FilePath "cmd.exe" -ArgumentList "/c `"$cmd`"" -NoNewWindow -Wait -RedirectStandardOutput "C:\Temp\command_output.txt" -RedirectStandardError "C:\Temp\command_error.txt"
    } catch {
        $_ | Out-File -FilePath "C:\Temp\remote_command_error.txt"
    }
} -ArgumentList $command

The file_log variable is used by my "Start LOG" task and arrives empty when executing the process. This is the error I'm getting:

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

Started:  12:58:39
Progress: 2024-12-18 12:58:40.81
   Source: Data Flow Task 
   Validating: 0% complete
End Progress
Progress: 2024-12-18 12:58:40.95
   Source: Data Flow Task 
   Validating: 100% complete
End Progress
Error: 2024-12-18 12:58:41.50
   Code: 0x00000001
   Source: Start LOG 
   Description: Empty path name is not legal.
End Error
Warning: 2024-12-18 12:58:41.50
   Code: 0x80019002
   Source: insert_data_mambu 
   Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  12:58:39
Finished: 12:58:41
Elapsed:  1.984 seconds

I tried putting the path to the file in double and single quotes but nothing works. Any advice is well-received!

Solution

  • The problem is that calling cmd.exe's CLI via the /C parameter doesn't support passing multiline code - everything after the first line is quietly ignored.

    If you want to keep your multiline source-code definition for readability / maintainability, you can programmatically transform it to a single line before use.

    That is, place the following statement before your try statement:

    # Transform the multiline source code to a single-line form.
$cmd = $cmd -replace '\^\r?\n'