Powershell from SSIS - Empty environment variable

HSB 25 Reputation points
2024-04-26T14:36:22.63+00:00

I'm running a script in an SSIS package and I specify an account to run the package in SQL Server Agent. When it runs, I am unable to get the value of $ENV:LOCALAPPDATA

Here's my code along with writing out the LOCALAPPDATA value

Start-Transcript -Path"\\........\log-test.txt"

Write-Output "Local App Data: "

Write-Output $ENV:LOCALAPPDATA

#Trust the repository used to prevent required user input

Set-PSRepository -Name"PSGallery"-InstallationPolicy Trusted

However, the log file outputs this and errors out when trying to use $ENV:LOCALAPPDATA to build a path for another variable using Join-Path because the value is blank.

Transcript started, output file is \xxxxxxxxxxxxxxxxx\log-test.txt

Local App Data:

PS>TerminatingError(Join-Path): "Cannot bind argument to parameter 'Path' because it is null."

If I log in as the user that the job is running as and then let the job run as scheduled, it works fine. Why is this field blank unless I'm physically logged onto the server as that user?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,777 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
2,091 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 33,211 Reputation points
    2024-04-28T01:44:26.0366667+00:00

    Hi @HSB,

    Seems like a permission issue.

    You may need to make sure that the account used to run the agent job has full permission to reach your source and destination such as the file path in your code.

    You may consider using proxy account.

    See running-a-ssis-package-from-sql-server-agent-using-a-proxy-account.

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.