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.
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-------------------------------------------------------------------
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
------------------------------
@BaileyHedstrom
Hello Bailey,
try it on this way:
It seems that $Args as undeclared parameters doesn't work in this context.
In your case PowerShellCode:
Best regards
Stefan
------------------------------
Stefan Schnell
Senior Systems Engineer at BWI GmbH
------------------------------
Hello Bailey,
try it on this way:
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
------------------------------
@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:
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
------------------------------
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------------------------------------------------------------------
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
------------------------------
