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.
is pronounced "ree-bar
" and is a "Modenism
" for R
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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)