Write-Progress status not displaying

I have code that creates a Process, starts it, and then, I want to view the progress. The process executes DTExec.exe, which runs SQL Server SSIS packages. In my case, a log table gets updated as each file is imported into a table. Thus, with the following code which combines the write-progress with a SQL query, I should be able to monitor progress. If I start my full script, and run the following code (with the supporting code to execute the SQL code and resolve variables) in either a Windows Powershell window or the Powershell ISE, I see the progress bar, but it does not display in the fully executing code. The full code basically does some up-front maintenance, creates a new System.Diagnostics.ProcessStartInfo object, populates it, creates a new System.Diagnostics.Process object, populates StartInfo, and starts the process. I then go into the code that should display the progress bar. I have attached the full code (which can’t actually be run without some additional data, but this is the progress bar code from it.

$tableCount = 0
$query = “SELECT COUNT(*) AS [Count] FROM dbo.ExtractLog WHERE ExtractDate >= '” + $Start + “’ AND TableName NOT IN (‘Start WV Extract’, ‘End WV Extract’)”
for ($i = 0; $i -le 42; $i = $tableCount)
{
$ctr++

	if (-not (Get-Process -Name DTExec -ErrorAction SilentlyContinue)) 
	{
		write-progress -activity "Import in Progress" -completed
    		break
	}
	if ($ctr % 8 -eq 0)
	{
		$result = Invoke-sqlcmd -serverinstance $FQDN -Database $SOURCE -Query "$query" -QueryTimeout 65535 -ConnectionTimeout 65535
    		$tableCount = $result.Count
		$percent = ($tableCount / 42) * 100
		write-progress -activity "Import in Progress" -status "$percent% Complete:" -percentcomplete $percent;
	}
	Start-Sleep -m 250
}

Hey Doug,

Just looking through your code I’m trying to understand the reference to 42. I assume that 42 is a static value for the number of tables. For instance, this loop:

for ($i = 0; $i -le 42; $i = $tableCount)

should look more like:

for ($i = 0; $i -le $tableCount; $i++)

The 'for' structure is variable start ; increment\decrement until ; increment\decrement by. Then you need to fix your math for percentage. If the table count is 42 and you are dividing by 42, then it's 100%. It's current table \ total tables, so:

$percent = ($i / $totalCount) * 100

Your code logic should be more like:

  • Define Variables
  • Run SQL query
  • $totalTables = $result.Count
  • Enumerate through results of SQL (for loop)

I beg to differ. $tableCount is the number of tables that have been processed. It starts at 0 and increments to 42. 42 is, besides being the answer to life, the universe and everything, the total number of tables that will have data imported. I could very easily set a variable, such as $maxTables = 42 at the start of the script and use $maxTables instead of 42, but that would not change the overall operation of the loop.

Unless I am mistaken, the examples I’ve seen for using the progress bar use a whole number from 0 to 100 to indicate percentage with the -percentcomplete parameter. I don’t think that is the problem.

The logic in the loop doesn’t make sense. You have a for loop using a variable $i. Then rather then increment or decrement the loop, you set it to $tableCount. The $i keeps resetting to 0 then to your $tableCount and $i is never used.

The magical 42 is relevant because in order to get progress you need to have a maximum number and current number to calculate a percentage. I was simply requesting requirements, not sarcasm. It appears you have two criteria:

  • Is DTEXEC running
  • Have 42 tables been processed

If you utilize a While loop you can check both criteria and if either DTEXEC is no longer running or your result indicates less than or equal than 42, exit the loop and complete the progress.

`
$results = 0
$percent = 0
Write-Progress -Activity "Starting process" -PercentComplete $results

While ( ((Get-Process -Name "dtexec" -ErrorAction SilentlyContinue).Count -gt 0) -and ($results -le 42) ) {
    write-progress -activity "Import in Progress" -status ("{0} Complete:" -f $percent) -percentcomplete $percent;
    
    $results = $results + 4 #Emulate a incrementing result from SQL
    $percent = [math]::round(($results / 42) * 100)
    
    Sleep 1
}

write-progress -activity "Import Complete" -Completed
`

Okay, I’m pulling out my hair. I’ve made some revisions per Rob’s suggestion, although it makes no difference.

I have attached my full script. There are two issues. 1) the write-progress never displays on screen and 2) the scripts just closes the PoSh window when done. If you examine the code, the catch{} block will display a message box and, if no error occurred, the finally{} block will display a message box that just says “Press ‘OK’ to Exit” Neither one of these gets displayed, although the log file indicates the script ran successfully with no errors.

If I am running the script and I open the either the PowerShell ISE or a PowerShell command window and run the code within the while{} loop (along with the ‘supporting’ code) a progress bar will display (graphical in ISE, text-based in regular PowerShell window), so I know it isn’t my code. It is as if the executing process is messing something up within the PowerShell script. Any ideas?

Reading your script I see this variable when you are starting the DTEXEC:

$waitforexit = $true

You are telling DTEXEC to start and the script that you do not want to proceed until DTEXEC has completed. When the script gets to the while loop, DTEXEC is complete, hence no progress. Can you try setting that to $false to see if you get the expected progress?

Good catch but not the issue. That is an orphaned variable. You’ll see that I am setting the variable, but never doing anything with it. I do not have a $p.WaitForExit(). The code has been greatly modified by me. Originally the code to start the process was a function and $WaitForExit was a parameter passed to it. I put the variable in originally when I was rewriting it but never removed it.

Gah, the server just ate my response. Hopefully I will not post a duplicate response. It appears the core (or one of the core) components of the script is to run DTEXEC and show progress. Open ISE, start an untitled.ps1 and pull only what is needed to accomplish your goal into the new script. This is how I usually write my scripts, I do proof of concept functionality and then I say, “What would cause this script to explode?” and start modularizing the code into functions and adding my try\catch logic. There is just a lot of variables and overhead in your script currently, although you’ll understand that more than me, I think the keep it simple philosophy will help get the bottom of your issue.

Okay, Rob, I am about ready to pull out my hair! I got it to work, kind of. The problem was that the two lines:
$output = $p.StandardOutput.ReadToEnd()
$errorText = $p.StandardError.ReadToEnd()

were before the while loop, thus (I assume, since I don’t know the internal workings, but I am a programmer) that PowerShell was waiting for the process to finish so it would read the output. I think that is a good assumption. I moved those two lines to after the while loop, thus the process has completed and there is output. Viola! I have a progress bar.
However, now I have another problem. It seems like consistently, the process will hang after processing only a few of the files. I have to close it in task manager. I’ve let it run for hours (normally takes about 3 minutes). Since I am using the code:
$proc.RedirectStandardError = $true
$proc.RedirectStandardOutput = $true
$proc.UseShellExecute = $false

there isn’t anything for me to see to determine what is going on, so I figured I would comment out those lines so I’d be able to see the window for the process running DTExec and troubleshoot. Well, guess what? It worked. Start to finish, progress-bar and all, in the expected timeframe. Now I am completely perplexed. If I leave it that way, there is a bunch of output, but it is not collected. If there is an error running DTExec, the process window will close, and that will be that. There won’t be any output to indicate what happened, since it was running in a different process window than PoSh. Got any ideas?
btw, thanks for getting me this far.