Brandie Tarvin (4/16/2014)
Another question. Once I finish writing up the Powershell script, how do I make it part of the SSIS package or the SQL Server Agent job?
Is it as simple as calling it in a .bat file or using the Execute Process Task?
You can do either one, it just depends on how you want to organize it.
If you want to keep it all in SSIS:
I would probably suggest using an execute process task
, it will ensure any errors with the PowerShell script itself trickle up so the task would fail properly.
If you want to use SQL Agent:
Setup your SQL Agent job with two steps, your first step is your SSIS package and then your second one will be a PowerShell type step. This will execute the script from the context of SQLPS. Because of that you will need to ensure your first line includes
$erroractionpreference = "Stop"
to ensure PowerShell errors cause the step to fail properly. By default it is set to "continue" which causes the step to show successful even though an error may have occurred.