Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Open Excel Error Using PowerShell in SQL Server Agent Job


Open Excel Error Using PowerShell in SQL Server Agent Job

Author
Message
Lrobinson 93181
Lrobinson 93181
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 140
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
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8394 Visits: 6168
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!!!
Lrobinson 93181
Lrobinson 93181
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 140
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")?
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8394 Visits: 6168
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!!!
Lrobinson 93181
Lrobinson 93181
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 140
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!
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8394 Visits: 6168
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!!!
Lrobinson 93181
Lrobinson 93181
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 140
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
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8394 Visits: 6168
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!!!
Lrobinson 93181
Lrobinson 93181
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 140
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.
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8394 Visits: 6168
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!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search