SSIS Execute Process Task executes Powershell script in Visual Studio but not when deployed

Question

I'm trying to create a SQL Server job with an Execute SSIS package step that will launch a PowerShell script and then do some file system tasks when completed (clean up the file system when the PowerShell script has completed successfully)

I originally tried using the PowerShell script feature in SQL Server's job setup, but I think the PowerShell environment in SQL Server doesn't completely line up to the locally-installed PowerShell environment, because I have a requisite locally-installed PSSnapIn that fails to be located by the SQL Server Agent PowerShell step when executed that way, but an SSIS Execute Process step doesn't appear to have that problem.

That said, the SSIS Execute Process step works when launched from Visual Studio (a command prompt window pops up displaying the execution where I can fully verify the process progress and completion), but does not work properly when deployed to the server. The SQL Server Agent step that launches the SSIS package reports the step completed successfully, but when I check the data to verify, it appears nothing has happened.

To configure the SSIS Execute Process step, I launch PowerShell in the "Executable" field and set the Arguments to (-ExecutionPolicy ByPass -command "path_to_my_ps1_script"):

Unfortunately, I'm not sure what other visuals I can provide to explain my question any clearer, but if there's something that would help, please let me know. I'm using PowerShell 4 to execute my script deployed on a Windows Server 2008R2 with SQL Server 2008R2.

Answer 1

I had a similar problem: When I execute the package from the catalog then it just do nothing, but when I executed it from the SQL Server Agent then it worked. Make sure that your SQL Server Agent Service account (the account that the step is running as) has the necessary access to the files and directories it needs to work with.