Search code examples
powershellssisresultsetexecute

Receive SSIS execution result from powershell

when I'm executing a SSIS package from powershell, the return data only consisting the SSIS process result not the actual result

I'm trying to get the resultset from SSIS process when it is done executing, I'm executing the packages from powershell by using PS' package.execute

#connecting to sql server
$sqlConnStr = "Data Source=" + $targetServer + ";Initial Catalog=master;Integrated Security=SSPI;"
$sqlConn = New-Object System.Data.SqlClient.SqlConnection $sqlConnStr

#create new SSIS object
$ssisService = New-Object $ssisNameSpace".IntegrationServices" $sqlConn
#select SSIS catalog
$cat = $ssisService.Catalogs["SSISDB"]
#select SSIS folder
$folder = $cat.Folders[$targetFolder]
#select target project
$project = $folder.Projects[$projectName]
#select target package
$targetPackage = $project.Packages[$package.PackageName]

#execute package and get the result
$actualVal = $targetPackage.Execute("false", $null)

expected value: the dataset from SSIS process

actual value: SSIS process result code only

Solution

  • in the end I dump the result into table and then select the result again after my SSIS process is done, just like Jacob said.

    thank you for your input.