Home

Powershell Execution Via SSIS Failing on Server

%3CLINGO-SUB%20id%3D%22lingo-sub-1272200%22%20slang%3D%22en-US%22%3EPowershell%20Execution%20Via%20SSIS%20Failing%20on%20Server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1272200%22%20slang%3D%22en-US%22%3E%3CP%3ETeam%2C%3C%2FP%3E%3CP%3EWe%20have%20requirement%20where%20we%20need%20to%20run%20the%20SSIS%20Package%20via%20Execute%20Process%20Task%20in%20SSIS.%20The%20powershell%20file%20has%20parameters%20which%20are%20passed%20within%20package%20at%20run%20time.%20When%20the%20code%20is%20run%20locally%20using%20SSIS%20package%20in%20VS%20or%20using%20Powershell%20command%20window%20then%20it%20runs%20well.%20However%20when%20deployed%20on%20Server%2C%20it%20gives%20below%20error%20(understand%20this%20is%20generic%20error)%3A%3C%2FP%3E%3CP%3EEPT_PowershellForExcelToSQL%3AError%3A%20In%20Executing%20%22C%3A%5CWindows%5CSystem32%5CWindowsPowerShell%5Cv1.0%5Cpowershell.exe%22%20%22-ExecutionPolicy%20Unrestricted%20-File%20D%3A%5CScripts%5Cxxxx%5Cxxxxxx.ps1%20-server%20xxx%20-database%20xxx%20-schema%20xxx%20-table%20xxx%20-ExecutionId%20xxx%20-xlsFile%20%5C%5Cxx%5Cxx%5Cyyy.xlsx%20-sheetname%20xx%20%7C%20Out-File%20-FilePath%20D%3A%5CScripts%5Cxxx%5Cxxx.txt%22%20at%20%22%22%2C%20The%20process%20exit%20code%20was%20%221%22%20while%20the%20expected%20was%20%220%22.%3C%2FP%3E%3CP%3ENote%3A%20I've%20replaced%20actual%20values%20with%20excel.%3CBR%20%2F%3EWe%20already%20applied%20the%20server%20settings%20as%20per%20article%20given%20at%20-%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsql-server-integration-services%2Frun-powershell-scripts-in-ssis%2Fba-p%2F388340%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsql-server-integration-services%2Frun-powershell-scripts-in-ssis%2Fba-p%2F388340%3C%2FA%3E%3C%2FP%3E%3CP%3EKindly%20advise%20if%20there%20is%20anything%20else%20we%20are%20missing.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1272200%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EIntegration%20Services%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Team,

We have requirement where we need to run the SSIS Package via Execute Process Task in SSIS. The powershell file has parameters which are passed within package at run time. When the code is run locally using SSIS package in VS or using Powershell command window then it runs well. However when deployed on Server, it gives below error (understand this is generic error):

EPT_PowershellForExcelToSQL:Error: In Executing "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "-ExecutionPolicy Unrestricted -File D:\Scripts\xxxx\xxxxxx.ps1 -server xxx -database xxx -schema xxx -table xxx -ExecutionId xxx -xlsFile \\xx\xx\yyy.xlsx -sheetname xx | Out-File -FilePath D:\Scripts\xxx\xxx.txt" at "", The process exit code was "1" while the expected was "0".

Note: I've replaced actual values with excel.
We already applied the server settings as per article given at - https://techcommunity.microsoft.com/t5/sql-server-integration-services/run-powershell-scripts-in-ssi...

Kindly advise if there is anything else we are missing.

Thanks