Execute Process Task, Passing in variables from SSIS to Powershell Script

I have been trying to build a package that can be called in an agent job to pass in paramaters for the purpose of backing up SSAS cubes. I have the code working but I can't seem to get the variables from SSIS to pass into the Powershell Script.

Here is the script with my latest attempt,

param($SSASServerName,$BackupLocation)
$serverName = $SSASServerName
$outputFolder = $BackupLocation 

## load the AMO and XML assemblies into the current runspace 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null 
[System.Reflection.Assembly]::LoadWithPartialName("System.Xml") > $null 
$dateStamp = (get-Date).ToString("yyyyMMdd") 

## connect to the server 
$svr = new-Object Microsoft.AnalysisServices.Server 
$svr.Connect($serverName) 
foreach ($db in $svr.Databases) 
{ 
  write-Host "Scripting: " $db.Name 
  $xw = new-object System.Xml.XmlTextWriter("$($outputFolder)DBScript_$($db.Name)_$($dateStamp).xmla", [System.Text.Encoding]::UTF8) 
  $xw.Formatting = [System.Xml.Formatting]::Indented 
  [Microsoft.AnalysisServices.Scripter]::WriteCreate($xw,$svr,$db,$true,$true) 
  $xw.Close() 
} 
$svr.Disconnect()

And in the Execute Process Task I try and bring them in the arguement field as C:\test\MDBackup.ps1 -$SSASServerName [User::SSASServerName] -$BackupLocation [User::BackupLocation]

What is the proper way to pass in SSIS variables to a powershell script running from an Execute Process Task?

May 17th, 2011 1:18am

You did not disclose how you coded the invocation of the PowerShell script. My thinking is you need to do it from within a DOS like command

powershell -command "& {C:\Path to your PS1 file\YourFileName.ps1 ServerName BkpLocation}"

Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 3:07am

did you set the argument property in the EXPRESSION ?
May 17th, 2011 4:53am

I was passing in the variables incorrectly and after looking at another script and constructing the expression properly it finaly worked.
Free Windows Admin Tool Kit Click here and download it now
May 19th, 2011 6:01pm

Can you please provide the expression that you put in the expression of Execute Process task of the SSIS package?

I am with the same situation right now. I am using the following

"C:\test\MDBackup.ps1 -$SSASServerName"+ [User::SSASServerName] +"-$BackupLocation "+[User::BackupLocation]

The task executes successfully but the backup does not happen.

I might not be passing the variables properly. Any help is appreciated.

Thanks.

May 12th, 2015 6:33pm

Hi SandyPe,

I had the same issue, though I am only using one parameter.  In SSIS using the expression for your Execute Process Task, assemble it together so that the argument looks like this as a pattern:

Executable: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Arguments: "& {S:\SOURCE_DB\Restore.ps1 DEV74}"

Notice the quotation marks around the entire string and the curly brackets around the path and the parameter.  However you build your variables make sure the evaluate to have that format: "& {PathTo.ps1 FirstParameter}"

I used this in my project as an example though it differs from your exact needs: @[User::RestorePSPath] +" "+ @[User::ServerName]+"}"+ @[User::Quote]  That final quote was needed so I created a variable just for that, I am sure there is a better way.

I hope that helps!

Thanks,

Verone

Free Windows Admin Tool Kit Click here and download it now
June 13th, 2015 5:39pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics