How to execute a Powershell script via SSIS

  • Perry Whittle

    SSC Guru

    Points: 233859

    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" 😉

  • stan.geiger

    Say Hey Kid

    Points: 677

    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: 996661

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Perry Whittle

    SSC Guru

    Points: 233859

    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" 😉

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

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