使用PowerShell远程执行DTSX报错：Empty path name is not legal

通过PowerShell从本地远程执行DTSX包时，User::file_log变量未正确传入执行过程，触发Empty path name is not legal错误。以下是执行命令、错误信息及解决建议：

执行命令示例 #

$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

错误信息 #

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

解决建议 #

• 修正参数传递方式，避免引号嵌套混乱：放弃通过cmd.exe中转，直接在远程PowerShell脚本块中调用DTExec.exe，用数组传递参数（PowerShell最佳实践），彻底解决转义问题。修改后的代码示例：

  $server = "server name"
  $credential = Get-Credential
  
  Invoke-Command -ComputerName $server -Credential $credential -ScriptBlock {
      param($dtsExecPath, $pkgPath, $fileData, $fileLog, $dbServer, $dbName, $dbUser, $dbPass, $dbTable, $days)
      try {
          # 用数组组织DTExec参数，避免引号转义问题
          $dtExecArgs = @(
              "/F", $pkgPath,
              "/SET", "\Package.Variables[User::file_data].Value;`"$fileData`"",
              "/SET", "\Package.Variables[User::file_log].Value;`"$fileLog`"",
              "/SET", "\Package.Variables[User::db_destination_server].Value;`"$dbServer`"",
              "/SET", "\Package.Variables[User::db_destination_name].Value;`"$dbName`"",
              "/SET", "\Package.Variables[User::db_destination_user].Value;`"$dbUser`"",
              "/SET", "\Package.Variables[User::db_destination_password].Value;`"$dbPass`"",
              "/SET", "\Package.Variables[User::db_destination_table].Value;`"$dbTable`"",
              "/SET", "\Package.Variables[User::days].Value;$days"
          )
          # 直接调用DTExec，合并输出到日志
          & $dtsExecPath @dtExecArgs 2>&1 | Out-File -FilePath "C:\Temp\command_output.txt"
      } catch {
          $_ | Out-File -FilePath "C:\Temp\remote_command_error.txt"
      }
  } -ArgumentList @(
      "G:\Program Files\Microsoft SQL Server\160\DTS\Binn\DTExec.exe",
      "G:\insert-data.dtsx",
      "\\servername\data.csv",
      "G:\insert_data_log.txt",
      "servername",
      "dbname",
      "username",
      "pass",
      "tablename",
      1
  )
  

• 检查SSIS包变量配置：确认User::file_log变量的作用域为包级别，数据类型为String，且EvaluateAsExpression属性设为False，避免变量被错误解析为空。
• 验证远程权限：确保执行PowerShell的账号对G:\insert_data_log.txt所在目录有读写权限，避免因权限不足导致路径无法正常写入。
• 添加调试输出：在远程脚本块的try开头加入调试代码，比如"Received file_log: $fileLog" | Out-File "C:\Temp\debug.txt"，确认参数是否正确传递到远程服务器。

内容的提问来源于stack exchange，提问作者Matias