cancel
Showing results for 
Search instead for 
Did you mean: 

VBO to Invoke PowerShell Files

StefanSchnell1
Level 7

Hello Community,

these days I tried to build my first own VBO. I thought a Code Stage that executes a PowerShell script might be a good example. So I developed a small routine that reads and executes a PowerShell script. It also offers the possibility to pass parameters to the script. Here the code:

//-Begin----------------------------------------------------------------

//-Initialization of output variables-----------------------------------
ErrorMessage = "";
Result = "";

//-Check if file exists-------------------------------------------------
if(!File.Exists(PowerShellFileName)) {
  ErrorMessage = "File not found";
  return;
}

//-Read the content of the file and check-------------------------------
string PSCode = File.ReadAllText(PowerShellFileName);
if(System.String.IsNullOrEmpty(PSCode)) {
  ErrorMessage = "Code missed";
  return;
}

//-Checking the Separator-----------------------------------------------
if(System.String.IsNullOrEmpty(Separator)) {
  Separator = ",";
}

//-Read parameters and add it to a list---------------------------------
object[] Params = null;
if(!System.String.IsNullOrEmpty(Parameters)) {
  string[] partsParam = Parameters.Split(new char[] {Separator[0]});
  Params = new List<string>(partsParam).ToArray();
}

//-Open the runspace----------------------------------------------------
Runspace runspace;

try {

  runspace = RunspaceFactory.CreateRunspace();
  if(STA == true) {
    runspace.ApartmentState = System.Threading.ApartmentState.STA;
  } else {
    runspace.ApartmentState = System.Threading.ApartmentState.MTA;
  }
  runspace.ThreadOptions = PSThreadOptions.ReuseThread;
  runspace.Open();

} catch(System.Exception ex) {
  ErrorMessage = ex.Message;
  return;
}

//-Create PowerShell----------------------------------------------------
System.Management.Automation.PowerShell PS;

try {

  PS = System.Management.Automation.PowerShell.Create();
  PS.Runspace = runspace;
  PS.AddScript(PSCode);

} catch(System.Exception ex) {
  runspace.Close();
  ErrorMessage = ex.Message;
  return;
}

//-Add parameters to PowerShell-----------------------------------------
if(Params != null) {
  foreach(string PSParam in Params) {
    string[] partsPSParam = PSParam.Split('=');
    PS.AddParameter(partsPSParam[0].Trim(), partsPSParam[1].Trim());
  }
}

//-Invoke PowerShell----------------------------------------------------
try {

  Collection<PSObject> Ret = PS.Invoke();
  runspace.Close();

  //-Set Result---------------------------------------------------------
  StringBuilder stringBuilder = new StringBuilder();
  foreach(PSObject oPS in Ret) {
    stringBuilder.AppendLine(oPS.ToString());
  }
  Result = stringBuilder.ToString();

} catch(System.Exception ex) {
  runspace.Close();
  ErrorMessage = ex.Message;
  return;
}

//-End------------------------------------------------------------------

There are the input parameters

  • PowerShellFileName = Path and name of the PowerShell script which should be executed.
  • Parameters = Parameters of the PowerShell script, if available.
  • Separator = The separator between the parameters, default is a comma.
  • STA = Single-Threaded Apartment if true, Multi Threaded Apartment if false, default is true.

There are the output parameters

  • Result = Output of the PowerShell script.
  • ErrorMessage = In case of error an explanatory text.

12672.png

To test it I use this PowerShell script example, with a few parameters and an output:

#-Begin-----------------------------------------------------------------

Param (
  [String]$var1,
  [String]$var2,
  [String]$desc,
  [Int]$i
)

$varHost = Get-Host;

$Ret = $Null;
For($j = 1; $j -le $i; $j++) {
  $Ret += "PowerShell greets " + $j + "`r`n";
}
$Ret += "Hello World via " + $var1 + " version " + $var2 + " from ";
$Ret += $varHost.Name + " " + $varHost.Version + "`r`n";
$Ret += $desc;
$Ret | Out-String;

#-End-------------------------------------------------------------------

12673.png

It works as expected. As far as I can see, this should make it easier to integrate PowerShell into Blue Prism processes. Since the basis is available with Windows, this approach should be executable without additional installations. In some cases, such integration can be very profitable, for example if consolidated scripts already exist and can be further used.

Best regards
Stefan



------------------------------
Stefan Schnell
Senior Systems Engineer at BWI GmbH
------------------------------

38 REPLIES 38

@Eric Wilson,

Yes, we can execute the Quest Command using the Windows PowerShell but the before the we need to import module.
I have completed that part and my commands work perfectly fine when I run manually using PowerShell but it do not capture the response when I try this object to run the command in Blueprism.

The Success and Error Message data Item both are completely empty.

Regards,
Vikash​

------------------------------
Vikash Anand
Senior Software Engineer
Ernst and Young
------------------------------

@StefanSchnell1

Yes, it is mandatory for us to run the Quest Commands​. When I run manually using PowerShell it works perfectly fine, 
Since, I need to capture the response too I was planning to use the above object. Though it does not pass any error message but also do not capture the response in the Success Data Item.

I have also posted my code above which I was using to capture the response. When it tries to run Quest command it fails with an error System.Linq missing.

Regards,
Vikash

------------------------------
Vikash Anand
Senior Software Engineer
Ernst and Young
------------------------------

Hi Stefan!

Thanks and appreciate your thuoghts, effort and sharing it here. I used your VBO as base and modified to our need. basically what we did is getting the PowerShell modules/ functions installed in the VDI/ mahines. Wrote a C# code that calls/ invokes the Powershell scripts/ function/ modules. It is working perfectly fine when w run in debugging mode (From Process studio). If we try to run the function or process in Control room, I am getting the following error though it works when called in debug mode and in Powershell application. The error is as follows. 

ERROR: System Exception : Error occurred while executing Powershell script. Please find the details below.
An error occured while executing code
Exception Details :The term 'userlist' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

This function basically gets the list of users from a DL and saves in CSV format in the location passed in the paramters. We pass the parameter as below.
csvfile=C:\Temp\ADD_Users\Input_File_Add_Users\Input_File_Add_Users_20210909.csv,Name=Previous,csvariable=DL_Name,LogLocation=C:\Temp\ADD_Users

This how it looks in Log from the automation run from control room.

Any help would be greatly appreciated. Thanks!



------------------------------
Sivakumar TK
Sr. Software Developer
PPD
Asia/Kolkata
------------------------------

BaileyHedstrom
Level 3
Hello, I am trying to use this object to run a PowerShell script that I need to pass two input parameters to. Here is how I have the parameters being passed. 
"D:\RCS_BluePrism\RQA Alerts\Input\Archive\PsTesting.xlsx , D:\\RCS_BluePrism\RQA Alerts\Input\Archive\PsTesting.csv"

Here is the code in the PowerShell Script:
Import-Excel $Args[0] | Export-Csv $Args[1]

When I step over the code stage, it produces this error. 
Could not execute code stage because exception thrown by code stage: Index was outside the bounds of the array.

I have no idea what is causing this and would like some help.
Thanks,
Bailey

------------------------------
Bailey Hedstrom
------------------------------

@BaileyHedstrom

Hello Bailey,
try it on this way:

12594.png
It seems that ​$Args as undeclared parameters doesn't work in this context.

In your case PowerShellCode:
"Param([String]$var1, [String]$var2);Import-Excel $var1 | Export-Csv $var2;"
Parameters:
"var1 = ""D:\RCS_BluePrism\RQA Alerts\Input\Archive\PsTesting.xlsx"", var2 = ""D:\RCS_BluePrism\RQA Alerts\Input\Archive\PsTesting.csv"""

Best regards
Stefan

------------------------------
Stefan Schnell
Senior Systems Engineer at BWI GmbH
------------------------------

Hi Stefan.

I was looking at your PS utility and it looks pretty cool. Thank you for putting the effort into this and sharing it with the community.

I've got a problem that I'm trying to overcome and was thinking that I might use PS to solve. I'd appreciate your input.

I have a very large collection (classic BluePrism problem) that I want to write to a CSV. I tried using the Excel VBO and it failed with an out of memory issue. I tried using the File Management VBO to write the file, that too failed.

Do you think it would be possible to input the collection into PS and then write out either a single CSV file or perhaps into smaller chunks using your VBO? 

I appreciate any help you could send my way.

jack

------------------------------
Jack Look
Sr Product Consultant
Blue Prism
------------------------------

@Jack Look

Hello Jack,
in my opinion you could use as an alternative a Code Stage instead a PowerShell​ script, to write a collection of any size to a CSV file. Here my try:

//-Begin----------------------------------------------------------------

void AddText(FileStream fstream, string value) {
  fstream.Seek(fstream.Length, SeekOrigin.Begin);
  byte[] info = new UTF8Encoding(true).GetBytes(value);
  fstream.Write(info, 0, info.Length);
  //fstream.Flush();
};

string[] columnNames = Collection.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToArray();
using (FileStream fs = File.Create(FileName)) {
  AddText(fs, string.Join(Delimiter, columnNames) + Environment.NewLine);
  foreach (DataRow row in Collection.Rows) {
    string[] fields = row.ItemArray.Select(field => field.ToString()).ToArray();
    AddText(fs, string.Join(Delimiter, fields) + Environment.NewLine);
  }
}

//-End------------------------------------------------------------------
​
Here the inputs:
12604.png
Here the references:
12605.png

Please try this solution with your use case and let me know your results.

fstream.Flush is not really necessary, with large amounts of data it could a reason for performance problems, so I have commented it out. I implemented it to make sure that the data is always written. The code is very simple, the collection is written line by line to the file stream. Since I don't have large amounts of data, I would be interested in the performance. For an approach with PowerShell it is not yet clear for me how to transfer, or better reference to, the collection to the PowerShell automation object.

I am looking forward to hear from you.

Best regards
Stefan

------------------------------
Stefan Schnell
Senior Systems Engineer at BWI GmbH
------------------------------

Hi Stefan.

Thank you very much for the detailed information; this is really great stuff. I will see how this goes and get back to you.

Cheers

jack


------------------------------
Jack Look
Sr Product Consultant
Blue Prism
------------------------------

Hi Stefan.

I was able to get it to work. Thank you for your help.

jack


------------------------------
Jack Look
Sr Product Consultant
Blue Prism
------------------------------

@Jack Look

Hello Jack,
thanks for your reply, great to hear that. Is the performance okay?
Best regards
Stefan​

------------------------------
Stefan Schnell
Senior Systems Engineer at BWI GmbH
------------------------------