jobudzin (11/20/2013)
That is what I was originally thinking, so then I am guessing I would need to create a brand new powershell script which encompasses the commands which I provided? I have been tinkering around with something like that to no avail. I would think this would be easier but since I am new to powershell it seems quite difficult. So from my understanding we cannot use the powershell type for a job step if it is going to execute powershell scripts, only lines of powershell code? That is the most frustrating part is that I have this working successfully using powershell but when it comes to implementing into a SQL Agent job it always becomes a bigger hassle and I end up spending most of my time with trial and error. I don't understand why we can just take the commands we ran in a powershell window, calling a script or not, and just paste into a SQL agent job step of type powershell and let it run. So I guess my biggest challenge, is how do i get my powershell which runs successfully into a SQL agent job? I have heard that the powershell type has limitations so I will use CmdExec, but it is still not working.
In my earlier post I explain why, in SQL Server 2008, the PowerShell experience in SQL Server Agent is not what it could be.
It might be of little consolation at this point, but the experience has improved in SQL Server 2012.
For your situation, one option is to save all your commands to a single PowerShell script and execute that using powershell.exe from a CmdExec step. This way all commands will execute within the same PowerShell session and in your example $dt will have a value when being passed into Write-DataTable. That's pretty much what I have resorted to doing to schedule my PowerShell scripts. I would also recommend using SQL Agent Proxy account so you can get granular on what permissions each script runs with.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato