Sad to say I have not done much with PowerShell scripts being called inside SQL Server. Nor am I at a computer that I could play with it right now.

I believe though instead of using the PowerShell type step that if you used CmdExec and just call your script as you would from a command line "powershell 'MyScript.ps1'" you could then pass a parameter that has the instance you are running from. Like "Powershell 'MyScript.ps1' MyInstanceName".

So the start of your script you have a param() setup to accept that value of MyInstanceName:


param(
   [Parameter(Position=0,Mandatory=$True)]
   [string]$InstanceName
)
#so if I wanted to use sqlcmd
sqlcmd -S $InstanceName -Q "SELECT @@VERSION"


As you started out stating the one step needed to know what instance it was on so the PowerShell script could call Foo.exe properly. However, later on you mention being able to pass the value to other steps. If this is true you might want to look at creating a small SSIS package that calls your PowerShell script and does whatever else you need. With SSIS you could be able to setup a global variable that the whole package could use.

SQL Agent powershell context reference
We are running Windows Server 2008r2 on VMware and are experiencing extremely high memory use when nothing is running. The server memory usages slowly creeps up to 98-99%. The server is configured to use 8GB of memory. Is there some setting we should be using so the server can better manage it's memory usage. It's behaving as if there is a memory leak.
