Sql-server – Running Powershell Code embedded in SSIS or TSQL, not in external file

powershellsql serversql-server-2016ssis

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

Best Answer

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.

Related Question