I recently made some enhancements to one of my scripts to take flattened published data and format it so that it's readable (while still flattened).
A little background where this might apply....
If you have many data values that need to be emailed out, it's not likely that you want to send an email for each value. Ideally, all of the values (filenames, database fields, etc.) would be emailed in a readable format in one email.
I most often use this PowerShell script immediately after a Query Database activity that has published data flattened seperated by "__%__" (without quotes). The Query Database activity would read multiple rows from a database that meet specified criteria. This particular example uses the Standard Logging IP by Charles Joy.
The published data passed into the Run .Net Script is the "Full line as string with fields separated by ';'".
Ex. Table Query Results:
Ex. Full line as string.... flattened with __%__ Published Data (not very pretty!)
6/14/2013 3:46:02 PM;3 - Failure;Server4;ActivityName: Copy File From A to B;ErrorSummary: Access Denied__%__6/14/2013 3:44:25 PM;3 - Failure;Server3;ActivityName: Copy File From A to B;ErrorSummary: File Does Not Exist__%__6/14/2013 3:42:07 PM;4 - Completed;Server2;Copy Completed;__%__6/14/2013 3:41:05 PM;4 - Completed;Server1;Copy Completed;
Posh Script to get the published data:
The Run .Net Script activity also has the published data flattened, but with line breaks. The table variable would then be sent in the email.
Ex.
6/14/2013 3:46:02 PM
3 - Failure
Server4
ActivityName: Copy File From A to B
ErrorSummary: Access Denied
6/14/2013 3:44:25 PM
3 - Failure
Server3
ActivityName: Copy File From A to B
ErrorSummary: File Does Not Exist
6/14/2013 3:42:07 PM
4 - Completed
Server2
Copy Completed
6/14/2013 3:41:05 PM
4 - Completed
Server1
Copy Completed
#######################################################################
$table = @()
$RawFailures = @'
<Published Data seperated by __%__>
'@
$Regex = [regex] '/*__%__'
$Failures = $Regex.Split("$RawFailures")
ForEach($Data in $Failures)
{
If ($Data.Split(";")[0].Length -ne 0) {
$Field1 = $Data.Split(";")[0]}
Else {$Field1 = $null}
If ($Data.Split(";")[1].Length -ne 0) {
$Field2 = $Data.Split(";")[1]}
Else {$Field2 = $null}
If ($Data.Split(";")[2].Length -ne 0) {
$Field3= $Data.Split(";")[2]}
Else {$Field3 = $null}
If ($Data.Split(";")[3].Length -ne 0) {
$Field4= $Data.Split(";")[3]}
Else {$Field4 = $null}
If ($Data.Split(";")[4].Length -ne 0) {
$Field5= $Data.Split(";")[4]}
Else {$Field5 = $null}
IF ($Data.Length -ne 0) {
$table += $Field1, $Field2, $Field3, $Field4, $Field5, ""
}
}
$table = @($table | Where-Object {$_ -ne $null})
#######################################################################
