April 16, 2014 at 11:53 am
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?
April 16, 2014 at 12:58 pm
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.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 17, 2014 at 1:49 pm
FYI: Followup Powershell question at http://www.sqlservercentral.com/Forums/Topic1562838-1351-1.aspx
If I weren't on a deadline of three days ago, this wouldn't be so hard and I'd already have my answer. I just know it... Sigh.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply