AnsweredAssumed Answered

Running a PowerShell script in SSIS package scheduled by SQL Server Agent

Question asked by Julie Hobert on Apr 1, 2016
Latest reply on May 13, 2016 by Pam Edmonston


Since our student information system is SQL Server based I have an SSIS package that pulls data to create the csv files for integration with Canvas.  The package creates user, course, section and enrollment files.  I then added an Execute Process Task to run the PowerShell script to zip the files and use the API to ship the zip file to Canvas.  When I run the package from within BIDS, everything works great.  The zip file gets created, the data is shipped through the API and the log file is created and filled.  However, when I then run the job via the SQL Server Agent I have problems.  The zip file is created and the log file is created, but not populated.  No data flows into Canvas.  Apparently the API portion of the script is not doing its thing.  The step in the SQL Agent job is running as the SQL Server Agent Service Account, however, I have given this account access to the folder where the data resides.  Does it need other rights?  Any thoughts?  Thanks.