Running Powershell Code embedded in SSIS or TSQL, not in external file

Question

Is there a method to embed powershell code in SSIS or T-SQL, without saving the code in an external file *.ps1? We know SSIS/Tsql can run powershell, however the code always seems to be saved somewhere else. We want powershell code embedded in SSIS package or t-sql stored procedure, which then can be executed.

Time issue with Dbas, setting up file share folders, and then deploying these powershell files into our Dev, QA, Prod environments (we have multiple servers), this can alleviate some of the Devops process.

1) The T-sql route may use xp_cmdshell which can be security issue, not sure if this is true. Is there safe way with xp_cmdshell or totally different route in T-SQL area?

2) The article below explains running Powershell in SSIS, however it is calling another file. Can we embed code in SSIS, and run powershell?

https://blogs.msdn.microsoft.com/ssis/2017/01/26/run-powershell-scripts-in-ssis/

At the end, I am trying to run a workflow parallel process. Powershell Run stored procedures in parallel in database

Workflow TestRunParallelExecute
{
    $ServerName = "localhost"
    $DatabaseName = "testrun"
    $Procedure_Query = "select name from sys.procedures"
    $Procedure_List = (Invoke-Sqlcmd -Server $ServerName -Database $DatabaseName -Query $Procedure_Query)

    ForEach -Parallel ($Procedure in $Procedure_List.Name)
    {
         Invoke-Sqlcmd -Server $ServerName -Database $DatabaseName -Query $Procedure 
    }
}
TestRunParallelExecute
cls

Answer 1

If you choose to use xp_cmdshell to invoke your workflow, see this answer for guidance (tl;dr: replace all your line feeds with spaces).

If you want to use the SSIS Execute Process Task to invoke powershell.exe without referencing a file, you can use the existing guidance, but instead of putting "-F D:\myScript.ps1" in the Process > Arguments dialogue you can put an ad-hoc script to be run (test with -C 'hello' | Out-File C:\temp\foo.txt) - although I'm sure there's a list of caveats and gotchas for actual implementation of this approach.

You can also create a Powershell SQL Agent job step and execute it either ad-hoc or on a schedule.

Answer 2

The best option to have the PS code packaged into the SSIS package would be to use a SSIS Script Task and run PowerShell host via C# to let it execute the code. There are various examples online around how to execute PowerShell code in C#. I have tried it a few times and have various experiences in client environments.

While the execute process task works it becomes a pain to handle modifying the code in that task editor. It is based on how complex your PS scripts end up being. Using the Script task it is easier to handle since you can use multiple lines to break up the PS code itself.