June 10, 2013 at 7:58 am
I am trying to run a Powershell script, which opens and modifies an Excel spreadsheet, from a SQL Agent job step, . The script runs fine by itself, and from BIDS. I have tried running as an SSIS pkg with a proxy, and when that didn't work I setup the step to simply run the Powershell script.
This is the error I receive:
A job step received an error at line 17 in a PowerShell script. The corresponding line is
'$Workbook=$objExcel.Workbookds.Open("\\files\reporting\Dailyreport_MMDDYYYY.xls",0,$False,1,"password")'
Correct the script and reschedule the job.
The error information returned by PowerShell is: "Exception calling "Open" with "5" arguments(s).
"Microsoft Excel cannot access the file "\\files\reporting\Dailyreport_MMDDYYYY.xls".
There are several possible reasons:
The file name or path does not exist (Yes, it does!)
The file is being used by another program (No, it’s not!)
The workbook you are trying to save has the same name as a currently open workbook." (No again, no workbooks open)
Process Exit Code -1. The step failed
It appears that it does not want to open Excel, but why?
FYI: 64-bit System, 64-bit SQL 2008 R2, 32-bit Office 2010.
I have already tried adding "Desktop" to SysWoW64 and editing my DCOM settings, with no change in results.
Thanks,
Lorna
June 11, 2013 at 7:11 am
I know that you need to run SSIS in 32-bit mode if you are using Excel to ensure that the ODBC (Jet) engine it uses (which is 32-bit) runs correctly. Have you tried running the 32-bit version of DTExec?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
June 11, 2013 at 9:52 am
Interesting, I tried that and now get this error:
"The process could not be created for step 1 of job...
The system cannot find the path specified" Step failed.
If I am understanding correctly, this means it could not locate (or access?) the powershell script (aka "The process")?
June 11, 2013 at 12:16 pm
I guess it really depends upon what Step 1 is. Have you tried to execute Step 1 outside of SQL Server Agent? What is it?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
June 11, 2013 at 12:32 pm
Hi, Step 1 is to execute the SSIS Package (using 32-bit DTExec per your suggestion)
"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /DTS "\MSDB\SSIS_Pkgs\ExcelModifer" /SERVER sqlreport /CHECKPOINTING OFF /REPORTING E
The package contains the Powershell script that opens and modifies Excel.
I have run the package from BIDS, and from Integrations Services with no problem.
I have run the Powershell script by itself with no problem.
However, when I run as a Package OR as a Powershell task in a SQL Job Step, I get the same result: Exception Error opening Excel.
I am using a Proxy, I have checked all the permissions I can think of, I have run all the suggestions for DCOM modifications and for creating a Desktop file.
At least this time it did something different, but I think it was a step backwards.
Any ideas are greatly appreciated at this point, Thank you!
June 11, 2013 at 12:50 pm
Have you tried to change the PowerShell script to a simpler one (that creates an empty file) to demonstrate that it isn't PowerShell per se that is the problem?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
June 11, 2013 at 1:37 pm
Hi,
I haven't but I wouldn't think it would get to line 17 of the script (see original post) if Powershell was the problem?
Are you thinking that if I can get Powershell to run using the 32-bit DTExec, then it will open Excel? I will try anything at this point.
Thanks
June 11, 2013 at 3:18 pm
Perhaps not but as you have moved to calling the 32 bit executor I would prove each step to confirm that it is working. Tackling it in one go is asking to get lucky.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
June 12, 2013 at 7:08 am
Hi,
Thanks again, it ran the script but wouldn't save:
"Exception calling "SaveAs" with "1" argument(s):"SaveAs method of Workbook class failed"
The script runs fine outside of job agent, it creates an excel workbook, adds data to cells, saves, and closes.
I tried to save to C:\Temp and also to \\files\reports (location of files on original script that won't open Excel).
I am not sure what this means, although it consistently fails to complete the script (for different reasons), it does appear to run the script.
June 12, 2013 at 7:12 am
Lrobinson 93181 (6/12/2013)
Hi,Thanks again, it ran the script but wouldn't save:
"Exception calling "SaveAs" with "1" argument(s):"SaveAs method of Workbook class failed"
The script runs fine outside of job agent, it creates an excel workbook, adds data to cells, saves, and closes.
I tried to save to C:\Temp and also to \\files\reports (location of files on original script that won't open Excel).
I am not sure what this means, although it consistently fails to complete the script (for different reasons), it does appear to run the script.
A quick search pops up this: http://vwiki.co.uk/Excel_(PowerShell)
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
June 18, 2013 at 9:53 am
Ok, I now see that the "Desktop" file workaround fixes a "save as" exception, and my exception occurs when opening the Excel file. I am going to convert my excel to csv and see if that cures the problem.
Thank you for all of your help!
July 17, 2013 at 3:27 pm
Lrobinson 93181 (6/18/2013)
Ok, I now see that the "Desktop" file workaround fixes a "save as" exception, and my exception occurs when opening the Excel file. I am going to convert my excel to csv and see if that cures the problem.Thank you for all of your help!
Hi , did you find a way to fix this ?
I am getting exactly the same error as
"Exception calling "SaveAs" with "1" argument(s):"SaveAs method of Workbook class failed"
The script runs fine outside of job agent, it creates an excel workbook, adds data to cells, saves, and closes.
-- My script also runs fine outside of an SQL agent job
-- Also tried the create Desktop folder option as listed earlier
I am not sure what this means, although it consistently fails to complete the script (for different reasons), it does appear to run the script
-- Same for me.
Appreciate any thing you found that made it work ?
cheers
July 18, 2013 at 6:56 am
Hi, I was not able to make it work from my system, however I did get it to work properly by putting the desktop file on the server where the database resides. I also installed the redistributable http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255 on that server, after making sure Office was installed there as well.
The job now works as scheduled, but if I try to run it manually from my system I still get the same "opening Excel" error.
Good luck, I know it took a long time and a lot of trial and error for me to find the solution that worked for me. Just keep applying all the suggestions everyone has made and eventually one of them will fit.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply