Standalone powershell script for pulling excel to sql server works and also when calling from Task scheduler, but powershell script fails when called from Sql Agent and SSIS(Sql Server Integration Service).
Error message received:
To do that I created a COM object that takes a url(it can be used with a local directory path as well).
$Excel = New-Object -ComObject Excel.Application
$workbook = $Excel.Workbooks.Open($filePath)
$Excel.Visible = $false
After knowing the issue, based on websites, I cleaned the office cache, killed an excel process in the background prior to the execution of this task, but my efforts were futile.
Besides, I split the whole powershell script into pieces and added to command pane in the settings for the step like the following:
Kindly wait for your guidance.