powershell - Add-Content -PassThru is writing to file but not passing output to console

Question

I've written a PowerShell 5.1 script to execute a SQL Server Integration Services (SSIS) package via DTExec.exe. This exports data from a database and writes it to a CSV file. DTExec.exe writes a lot of information to stdout which may be useful for debugging if anything goes wrong, so I'd like to display this information in the console but also write it to a log file for a permanent record.

Reading and experimenting I've come up with using cmd.exe to execute DTExec.exe, to ensure any error gets written to the log file in the correct place. See http://chuchuva.com/pavel/2010/03/how-to-redirect-output-of-console-program-to-a-file-in-powershell/ for details. Also, I had to use Add-Content -PassThru to write the output to both the log file and the console rather than Tee-Object because Tee-Object encodes its file output as UTF-16 whereas the log file will be encoded as UTF-8.

Experimenting with a test application this works fine - it writes to both the console and the log file. And error information appears in the correct place in the log file. However, when I try to do the same thing with DTExec.exe it only writes to the log file, and does not echo that output to the console.

Can anyone see what I'm doing wrong?

(by the way, the code is generating the CSV file as it should. Everything seems to work correctly apart from echoing the output of DTExec.exe to the console)

Here's the code:

$LogFilePath = 'D:\Test\Logs\Result_20201028.log'

$cmd = "`"$DTExecPath`" -File $SsisPackagePath"
$cmd += " -Connection `"$sourceDbConnectionManager`";`"$sourceDbConnectionString`""
$cmd += " -Set \Package.Variables[User::StartDate].Value;$formatedStartDate"
$cmd += " -Set \Package.Variables[User::EndDate].Value;$formatedEndDate"
$cmd += " -Set \Package.Variables[User::DestinationFileName].Value;$CsvFilePath"

# Backticks in 2`>`&1 ensure the redirection is done by cmd.exe, not PowerShell.
cmd /c $cmd 2`>`&1 | Add-Content -Path $LogFilePath -PassThru

The $cmd variable resolves to (line breaks added for clarity):

"C:\Program files\Microsoft SQL Server\150\DTS\Binn\DTExec.exe" 
    -File D:\Test\Export.dtsx 
    -Connection "Reporting Database Connection Manager";"Data Source=TestServer;Initial Catalog=Reports;User ID=ExportUser;Password=*******;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=True;" 
    -Set \Package.Variables[User::StartDate].Value;2020-10-27 
    -Set \Package.Variables[User::EndDate].Value;2020-10-28 
    -Set \Package.Variables[User::DestinationFileName].Value;D:\Test\Dropbox\Export.csv

标签： powershell