SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps
Since you are using SQL Server 2008 R2 you can simply create your SQL Agent Job with the step configured as a "PowerShell" type, instead of trying to use the CmdExec. You only need to include the "meat" of your script. Since you are using the PowerShell type of a SQL Agent step it has already called the powershell.exe and imported the SQLPS module for you. So an example:


With your code I believe you can simply configure the step as shown below: