PowerShell Script 'Hangs' in 3.0

The attached script worked fine in 2.0, however the progress bar did not display. The server was running 2.0 and I upgraded to 3.0. At that point in time, the script no longer runs. It starts out, displays the progress bar, with 0% complete, and then hangs.

If I check task manager, I can see DTExec.exe running, and the data all gets imported, but the script shows no progress. I have to finally kill it with a Ctrl-Break.

Also the script works fine on another server running 3.0.

The script is relatively generic, but via the parms file, it executes DTExec.exe (32-bit) with parameters to execute an SSIS package. There were no problems until I upgraded the other server with PS 3.0. On both servers the version is 3.0, Build = -1, Revision = -1.

I've included the log file from a good run.
0002.ImportData.txt
0002.ImportData-20131212-111527.log
LVL 15
dbbishopAsked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
I suspect the Invoke-SqlCmd being blocked, though you are not using locks and allow for dirty reads in that query. Did you check whether that cmdlet returns? Since you have issues with that particular machine with both PS2 and PS3, something here seems to be buggy.

Other than that, some parts seem to be overly complicated. Instead of the System.Diagnostics classes, you could just use PS background jobs, which are much easier to handle.
Also you are checking modules and ODBC on each call of GetParameterValue, instead only once at start of the script. I understand you want to encapsulate the check, but I would do so in another function you only have to call once.

IMHO, this part:
	$msg = "In order to properly import data, the Microsoft Office System `n"
	$msg += "Driver, Data Connectivity Components must be installed.`n"
	$msg += "These drivers allow import of data from Microsoft sources (e.g.`n"
	$msg += "Excel spreadsheets, Access databases, etc.). The components`n"
	$msg += "can be downloaded from Microsoft at the following link:`n"
	$msg += "http://www.microsoft.com/en-us/download/details.aspx?id=23734`n`n"
	$msg += "Would you like to download and install these components now`n"
	$msg += "(you must have Internet connectivity to download)?"

Open in new window

would be better written as
	$msg = @"
In order to properly import data, the Microsoft Office System
Driver, Data Connectivity Components must be installed.
These drivers allow import of data from Microsoft sources (e.g.
Excel spreadsheets, Access databases, etc.). The components
can be downloaded from Microsoft at the following link:
http://www.microsoft.com/en-us/download/details.aspx?id=23734

Would you like to download and install these components now
you must have Internet connectivity to download)?
"@

Open in new window

0
 
dbbishopAuthor Commented:
For some reason localhost did not work. I needed to use the server name. The server name is one of the values read from the parms.csv file.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You should definitely look at that. Does it work if you use the "." for the server, or "(local)"? Maybe shared memory or Named Pipe is excempt, and you need to use TCP/IP for MSSQL.
0
 
dbbishopAuthor Commented:
It was no problem. Made to be portable so server name is in a parameter file. 'localhost' worked on the other servers I ran the code against.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.