I am testing to see if it is possible to capture PS output in SSIS. I am an SSIS newbie. I created a script with one line of code:
$result = Write-Output "Exit"
I can run test.ps1 successfully from an Execute Process Task.
Here is how I set up the Execute Task:
Executable = %SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe
Arguments = -file "C:\Scripts\testing\SSIS\SSIS_Test.ps1"
StandardOutputVariable = User::Result
User::Result Variable I created in the Execute Task:
Container = Package
Name = Result
Namespace = User
Value Type = String
I have created an Execute SQL Task to insert the results in a test database. Using:
SQLSourceType = Direct input
SQLStatement = INSERT INTO dbo.Table_1(test) VALUES (?)
and a Parameter Mapping in the Execute SQL Task
VN = User::Result
Direction = Output
Data Type = Long
Paramter Name = 0
Parameter Size = -1
The workflow runs but the output is a null value.
How can I capture the output of $Result for the PS script into a variable in SSIS and use that variable to update a DB??