I’ve found myself using using powershell much more frequently to do stuff I used to do with SSIS just because its fast to develop and less complicated to deploy.

The one issue that really had me puzzled was when I started noticing that scripts that I had tested successfully from Powershell ISE were failing on deploy to QA or UAT.

The answer I found out after a lot of confusion was that Windows was at PS 4.0 but SQL was still using PS 2.0.


This article was very helpful in a workaround by launching a new instance of powershell v 4.0 from PS 2.0 but the drawback is now you have to go on the server and manipulate files which in a locked down production environment is time consuming and defeats using powershell with SQL Server Agent in the first place.