Help with a simple TSQL script to open and save an Excel file.

Question

Help with a simple TSQL script to open and save an Excel file.

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Phil Parkin - Thursday, May 3, 2018 2:09 PM
sgmunson - Thursday, May 3, 2018 1:56 PM
None of those things are what I'm suggesting. SSIS has an item called a Script Task, that let's you write code in either VB or C#. It usually defaults to the latter, so you would have to choose VB. You then Edit the script and use that VBScript code as a starter, and it may run fine as is, but might need a small modification, and I'd also suggest adding additional lines that just Set objVariable = Nothing. Substitute the name of the 3 object variables that get Set in your existing script for where I show objVariable.
I don't think that it will be as simple as that.
The OP's original script looks like VBScript to me, whereas the SSIS Script Task uses VB.NET.
I'd like to rule out this being a permissions problem. Karen, are you able to change the SQL Agent service's credentials to use your account and perform the test again?
I'm also wondering whether the SQL Agent service user needs the 'log on interactively' policy to be granted in order to be able to create an instance of the Excel application.

Yes, that's a good point. However, most object-oriented VBScript will run un-changed in either VBA or in VB.Net, or will do so with minor changes. In VBA, I know you would need to add the Excel object to "references", but I don't recall that being needed in a VB Script Task within SSIS.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

x SSC-Insane Points: 23660 More actions · Answer 2

How about something along the lines of putting this in your batch file then running it from the agent as an xp_cmdshell job:

cscript "C:\Documents and Settings\jmunn\My Documents\Visual Studio 2008\Logs\purgeLogs.vbs"

from:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d4eb7a73-0549-413f-811e-0105f9861cea/vbscript-runs-correctly-from-command-prompt-but-not-from-sql-server-agent-job?forum=sqlgetstarted

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Karen Grube - Thursday, May 3, 2018 1:54 PM
I'm not sure about using the script task, but it can't hurt to try. I've just never done that before and I have no clue what the script for the script task should look like. WOW! This shouldn't be that hard. I'll try to figure it out.

It's because it's not something that is supported by Microsoft so those things generally aren't easy. It's documented somewhere (it used to be easy to find) that they don't support using automation of office applications from any unattended or non-interactive client application or component.
And now I found the documentation...it's a link in this blog. The blog is worth reading though and it has a suggested workaround which is worth a try:
Unable to open excel files using a cscript in SQL Server Jobs

Sue

Phil Parkin SSC Guru Points: 246961 More actions · Answer 4

I tried pasting the code into a VB.NET Script Task:

As you suggest, it needs some tweaks to make it parse in .NET, and I think it will need a suitable Excel reference to be added.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lowell SSC Guru Points: 323518 More actions · Answer 5

it's gotta be permissions.
here's my suggestion:
On the SQL Server, Create a credential that has your actual username and password, because we know it works when you execute it.
In SQL Agent, create a new proxy that uses that credential.
Change the job so that instead of using the default
execute the job.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Karen Grube SSC Eights! Points: 994 More actions · Answer 6

Yes, I just did that, restarted the SQL Server Agent, and it's still not working . . . I created a credential called SQL_Admin using my windows login. To be clear, I have 'sa' privileges on the server. Then I created two proxies under the name SQL_Admin using that credential.
Again, it executes, but does nothing. I actually have two jobs, one using command.exe and one trying to run the DTS Package I created. Both supposedly run under the proxy, but neither is working.

Anything else I should try? Would it be necessary to restart SQL and not just the agent? I'm at a loss here.

Karen Grube SSC Eights! Points: 994 More actions · Answer 7

Okay, so I changed the SSIS package to a script.

It works perfectly in Integration Services. I deployed it and I can execute from the catalog and it runs perfectly.

I tried adding an Execute Integration Services Package step to a new job to tun the package, and IT STILL WON'T RUN from the Agent.

I've tried running it under the SQL Agent Account and the new Proxy I created, and IT WILL NOT RUN!

Now the error I get is "Exception has been thrown by the target of an invocation." Yeah, that's a helpful message. Yeash!

Any suggestions I tried looking up that error message and didn't find anything helpful.

Karen Grube SSC Eights! Points: 994 More actions · Answer 8

So, now I've even created a stored procedure that executes the DTS SaveExcel package. It runs perfectly from SQL,

I have a tsql step executing the stored procedure as my login. All it does now is hang.

What is going on? Good grief. I really can't figure this out at all.

Karen Grube SSC Eights! Points: 994 More actions · Answer 9

The initial problem was simply the complexity of the report. I had rewritten it three times, but because of all the bookmarks, exporting to 32 tabs, and lots of formatting, it just will not import correctly to be converted to a Google Sheet.

But I did get it to work by doing the following.

I export the file to a folder location from SSRS.

I wrote a DTS script to simply open the exported Excel file and save it back.

I run the DTS package and it saves the file back perfectly.

At that point, I can upload it correctly to Google Sheets through another DTS package I wrote.

The big problem was getting the DTS package to run from the SQL Server Agent. It would run from TSQL, but it just wouldn't run from a job.

I finally called in the cavalry, a terrific consultant who's helped me figure things out before. After nearly two hours of trying different things, he finally figured out that two folders have to be in place in order to run DTS packages from the SQL Server Agent. This makes no sense to me, but creating these two empty folders worked. The process now runs correctly without failing. Here are the two folders.

C:\Windows\SysWOW64\config\systemprofile\Desktop

C:\Windows\System32\config\systemprofile\Desktop

I hope that helps someone.

Sue_H SSC Guru Points: 90860 More actions · Answer 10

Karen Grube - Monday, May 7, 2018 9:09 AM
The initial problem was simply the complexity of the report. I had rewritten it three times, but because of all the bookmarks, exporting to 32 tabs, and lots of formatting, it just will not import correctly to be converted to a Google Sheet.
But I did get it to work by doing the following.
I export the file to a folder location from SSRS.
I wrote a DTS script to simply open the exported Excel file and save it back.
I run the DTS package and it saves the file back perfectly.
At that point, I can upload it correctly to Google Sheets through another DTS package I wrote.
The big problem was getting the DTS package to run from the SQL Server Agent. It would run from TSQL, but it just wouldn't run from a job.
I finally called in the cavalry, a terrific consultant who's helped me figure things out before. After nearly two hours of trying different things, he finally figured out that two folders have to be in place in order to run DTS packages from the SQL Server Agent. This makes no sense to me, but creating these two empty folders worked. The process now runs correctly without failing. Here are the two folders.
C:\Windows\SysWOW64\config\systemprofile\Desktop
C:\Windows\System32\config\systemprofile\Desktop
I hope that helps someone.

That is exactly what the workaround is in the link I provided for you earlier in this thread.
Unable to open excel files using a cscript in SQL Server Jobs

Sue

Karen Grube SSC Eights! Points: 994 More actions · Answer 11

Although the article mentions them, both didn't seem relevant because I'm working on a 64-bit machine, and the article mentions one only being required on a 32-bit machine. Apparently, that isn't true. You need both on a 64-bit machine.

Sue_H SSC Guru Points: 90860 More actions · Answer 12

Karen Grube - Tuesday, May 8, 2018 5:04 AM
Although the article mentions them, both didn't seem relevant because I'm working on a 64-bit machine, and the article mentions one only being required on a 32-bit machine. Apparently, that isn't true. You need both on a 64-bit machine.

That's odd - Maybe you are thinking of a different article. The post lists the fix for both 32 and 64 bit so it's relevant for 64 bit.
On 64 bit, I only have the one as listed in the post - I did not need both. Just one.

Sue