Clean way to run PowerShell scripts in SSIS

  • BrownCoat42

    SSC Journeyman

    Points: 87

    I am bringing in a number of PowerShell scripts as a process task into an SSIS package to simplify scheduling of a daily process I run that currently has many separate steps of dependencies in separate jobs. when we have to run the process manually we have to run tasks on 4 different servers and no matter how many times we do it, it is still confusing and sometimes miss a step.

    I am using scaleout SSIS and what I am currently thinking is to store by powershell script on a file share and point to the file share location in the process task so that I don't have to keep separate local copies on each SSIS worker.

    It has been a point to eliminate any other .net code from our packages as we do not consistently have staff available who can support it while PowerShell is ubiquitous.

    I would really like to get the process to the point of being completely self-contained so that in the future, no one has to remember to deploy an outside ps1 file to whatever location it is stored in, and then also keep the script within the SSIS catalog.

    Has anyone else come up with anything better than this?

  • BrownCoat42

    SSC Journeyman

    Points: 87

    Ok, just made some progress. I found an old forum post where someone was executing batch files dynamically by passing the commands as variables and it gave me an idea.

    What I did, was paste my powershell script into a string variable prepended with -WindowStyle Hidden -NoProfile -Command "powershell script text here"

    I had problems initially with getting the script to interpret the statements individually, and after 7 years of doing PowerShell, learned today that PowerShell had a statement terminator of semi-colon. My ending each statement with ;, the whole script was able to run inline and eliminate the need for an external reference to a script file. I love this discovery as whenever I go from SQL to powershell I usually drive myself nuts terminating statements with semicolons for the first couple hours and deleting them, but now I will just leave them in place! (I probably wont stop switching 'print' and 'read' or 'select' and 'get' anytime soon)

    Unfortunately storing the command in a variable makes the PowerShell script basically unreadable which is a bit of a bummer if you need to debug or modify the script after, its really easier to go back to the original script, modify it in the ISE and then paste it back in.

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

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