How to execute a Powershell script via SSIS

Question

How to execute a Powershell script via SSIS

Perry Whittle

SSC Guru

Points: 233846
More actions
December 3, 2015 at 12:06 am

#313579

Comments posted to this topic are about the item How to execute a Powershell script via SSIS
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

stan.geiger Right there with Babe Points: 727 More actions · Answer 1

Personally I think executing Powershell scripts within SSIS sets a really bad precedent. There is probably a better solution. Just my opinion, but I would never allow it in my shop. Just because you can doesn't mean you should. SSIS is an ETL tool not a programming language. If you need to use PoSH embedded to start turning things on and off or get WMI type stuff then you need to think through your solution.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

stan.geiger (12/3/2015)
Personally I think executing Powershell scripts within SSIS sets a really bad precedent. There is probably a better solution. Just my opinion, but I would never allow it in my shop. Just because you can doesn't mean you should. SSIS is an ETL tool not a programming language. If you need to use PoSH embedded to start turning things on and off or get WMI type stuff then you need to think through your solution.

Ask anyone and they'll tell you that I'm no fan of PoSh or SSIS. That, notwithstanding, I can actually see why you might want to call PowerShell from SSIS. For example, you may want to run a file through the gambit of a CSVIMPORT followed by a CSVEXPORT to "normalize" the delimiters and double quotes in an otherwise difficult to import file. Yes, I suppose that you could import the file using one of many tricks in SSIS but PoSh would make things easy for such a thing.

I can also see calling PoSh loop to step through servers via WMIC to gather up server health information (for example, free space, % Free, and "IsDirty" bits for all disks on all servers) and have SSIS dump it to a table.

Remember that SSIS isn't just an ETL tool. It's also a "Process Control" tool.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 233846 More actions · Answer 3

stan.geiger (12/3/2015)
Personally I think executing Powershell scripts within SSIS sets a really bad precedent.

that's your opinion and you're entitled to it.

stan.geiger (12/3/2015)
There is probably a better solution. Just my opinion, but I would never allow it in my shop. Just because you can doesn't mean you should.

there may well be a better solution, the guide shows how to execute a posh script as this is something that I've seen issues with across many forums.

stan.geiger (12/3/2015)
SSIS is an ETL tool not a programming language.

no it's not a pure ETL tool, as mentioned already it's a process control tool

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉