May 2, 2018 at 11:08 pm
I have an SSRS report that exports to a specific folder on a scheduled basis. There's a problem with the exported file, though, in that it cannot be opened as a Google Sheet. Yes, I've rewritten the SSRS Report three times to try to get it to export properly. It's just complex and won't export to Excel in a manner that Google Sheets can upload or convert. However, if I simply open the exported Excel file manually and save it back, the file will open up perfectly in Google Sheets.
So . . . I found a little tiny vb script to open the file and save it.
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\SSRS_Exports\Myfile.xlsx")
Set objRange = objworkbook.worksheets(1).columns(1)
objworkbook.save
objexcel.quit
This is saved as a .vbs file in the same C:\SSRS_Exports folder as the Excel file.
BUT, when I try executing the .vbs program from a SQL Server job step, the step succeeds, but the .vbs program is never executed. When i run it manually, I can see the date modified of the file change and I know it's been saved back (because I can then manually upload it to Google Sheets.)
I've tried at least five different ways to set it up in a SQL Server Agent job step: As a SQL command using ..xp_CMDShell , as an Operating System Command . . . Neither works.
I've quadruple checked permissions on the folder, the Excel file, AND the .vbs file - That shouldn't be the issue. Yes, I'm specifying the correct file location. Yes the server is set to run xp_CMDShell.
I've gone back and forth with different "run as" options, but absolutely nothing works.
Yeash! All I want to do is open the file and save it back.
I've thought about writing a little stored procedure to do that, but every post I find focuses on importing data or exporting data, and this isn't that kind of file at all. I can't find a post or article that goes over just opening an Excel file and saving it. I've even looked at writing a DTS package to do this, but that involves more complicated scripting and it seems like overkill for this simple task.
So, if anyone has any specific advice that would be great. Or, if someone can point me to some simple tsql code for opening and saving a current-version Excel file, that would also be very helpful. As it is, I'm stuck doing this manually, and this has to be automated.
THANK YOU!
PS. I also posted this in the SQL Server Agent forum because I wasn't sure if the Stored Procedure solution would be easier than trying to bluster SQL Server Agent into running the .vbs script. I hope so.
May 3, 2018 at 2:19 am
Do you have Excel installed on the machine you are trying to run this from?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 3, 2018 at 10:24 am
Yes, of course Excel is installed or I wouldn't be able to open the file manually and save it as I mentioned. That isn't the issue.
May 3, 2018 at 11:00 am
Karen Grube - Thursday, May 3, 2018 10:24 AMYes, of course Excel is installed or I wouldn't be able to open the file manually and save it as I mentioned. That isn't the issue.
It was not intended as a stupid question.
I was assuming that you were developing locally and testing on a server!
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 3, 2018 at 11:09 am
I have spent HOURS searching for an answer on Google or I wouldn't be asking here. All I can find are really complicated scripts that import or export data. That isn't what I'm trying to do at all. I apologize about the code formatting. I'll do that from now on.
May 3, 2018 at 12:24 pm
I get the funny feeling that somewhere there's an assumption that isn't making it's way into the open. If you run an SSIS job via the SQL Agent, you are probably going to have that job executing on the server on which SSIS is installed, and then, when the SSIS package runs, it's going to try and run that script locally to that server, so if that server doesn't have Excel installed on it, then it's not going to work. It might not fail, either. If you develop on your laptop or desktop, having Excel installed on that desktop or laptop is not going to help at all under those circumstances. Hopefully, that at least clears up what Phil was trying to say.
If you run the SSIS package locally, it will only run correctly if you have a local instance of SSIS installed, and Excel. Both would be required wherever that package runs. Does that help at all?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 3, 2018 at 1:19 pm
I have both Excel and SQL Server installed on the server. I am working directly on the server.
On the server, I can run the SSIS package directly from the catalog and it runs perfectly. The Excel file is opened and saved back. This just isn't executing when I run it from the SQL Server Agent.
I have a single job with one step that simply executes the SSIS package - or it says it does. It just isn't actually doing anything. I've done everything I can think of regarding permissions, and I don't think that's the issue. I just don't know what the problem is.
I also tried using an Operating System Command step to run the little executable I created to open and save the file (the same .exe file that the SSIS package successfully runs) and that isn't working either. The step succeeds, but the executable apparently isn't run. The simple command I'm using in the job step is C:\SSRSExports\SaveFile.exe That's the location of both the executable and the Excel file, only the date isn't being changed when I run the job the way it does when I run the executable directly. I'm running the job as the SQL Server Agent Service Account, which has permissions set appropriately for the folder and the executable.
I ALSO tried setting up a transact SQL step that uses cmd.exe, and that didn't work either.
So, I was trying to get around this by maybe writing a little stored procedure that ONLY opens an saves the excel file, but I can't figure out how to do that without setting things up to import and export data, which is NOT what I need to do. This isn't a data spreadsheet. It is a highly-formatted export from SSRS.
Any guidance would be greatly appreciated.
May 3, 2018 at 1:27 pm
Karen Grube - Thursday, May 3, 2018 1:19 PMI have both Excel and SQL Server installed on the server. I am working directly on the server.On the server, I can run the SSIS package directly from the catalog and it runs perfectly. The Excel file is opened and saved back. This just isn't executing when I run it from the SQL Server Agent.
I have a single job with one step that simply executes the SSIS package - or it says it does. It just isn't actually doing anything. I've done everything I can think of regarding permissions, and I don't think that's the issue. I just don't know what the problem is.
I also tried using an Operating System Command step to run the little executable I created to open and save the file (the same .exe file that the SSIS package successfully runs) and that isn't working either. The step succeeds, but the executable apparently isn't run. The simple command I'm using in the job step is C:\SSRSExports\SaveFile.exe That's the location of both the executable and the Excel file, only the date isn't being changed when I run the job the way it does when I run the executable directly. I'm running the job as the SQL Server Agent Service Account, which has permissions set appropriately for the folder and the executable.
I ALSO tried setting up a transact SQL step that uses cmd.exe, and that didn't work either.
So, I was trying to get around this by maybe writing a little stored procedure that ONLY opens an saves the excel file, but I can't figure out how to do that without setting things up to import and export data, which is NOT what I need to do. This isn't a data spreadsheet. It is a highly-formatted export from SSRS.
Any guidance would be greatly appreciated.
Would an SSRS subscription to that report be an alternative? I'm not 100% sure, but when you manually run that package from the catalog, you have a user context that can process the opening of Excel. When you run under the SQL Agent, you don't get one, so SSIS might have to use that VB code (possibly needing small modifications) within a Script Task in order for it to work.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 3, 2018 at 1:37 pm
Yes, I'm using an SSRS subscription to export the excel file. The problem is that SSRS exports it badly for some reason and the only way I can get it to convert to a Google Sheet is by opening the file first and saving it back, which I shouldn't have to do manually if I can open it and save it back through my little executable or through a TSQL Script. ALL that's necessary for the Excel export to be compatible with Google Sheets is to open the file and save it, which the executable does perfectly. I just can't get the executable to run from the Agent no matter what I do and I don't know why.
May 3, 2018 at 1:46 pm
Karen Grube - Thursday, May 3, 2018 1:37 PMYes, I'm using an SSRS subscription to export the excel file. The problem is that SSRS exports it badly for some reason and the only way I can get it to convert to a Google Sheet is by opening the file first and saving it back, which I shouldn't have to do manually if I can open it and save it back through my little executable or through a TSQL Script. ALL that's necessary for the Excel export to be compatible with Google Sheets is to open the file and save it, which the executable does perfectly. I just can't get the executable to run from the Agent no matter what I do and I don't know why.
Have you tried what I suggested with SSIS - using a Script Task as the means of running that VBScript code ? You might have to make small changes to make it work, and you'll want to also set all those object variables to Nothing at the end of the script.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 3, 2018 at 1:46 pm
TO BE CLEAR . . . I created a .vbs script that opens the file and saves it. THAT won't run in an Agent step, but it runs when I manually execute it.
SO . . . I created a .bat file to run the vbs script. THAT didn't run either from an Agent step but it runs when i manually execute it.
SO . . . I ran a little utility that turned the .bat file into a .exe file THAT Operating System step says it succeeds, but it does nothing. However, the executable runs when I manually execute it.
SO . . . I created an SSIS package to run the executable. THAT succeeds, but it does nothing when run from an Agent step but it runs when i manually execute it from the catalog.
I'm really getting frustrated. I hope we can figure this out.
May 3, 2018 at 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.
May 3, 2018 at 1:56 pm
Karen Grube - Thursday, May 3, 2018 1:46 PMTO BE CLEAR . . . I created a .vbs script that opens the file and saves it. THAT won't run in an Agent step, but it runs when I manually execute it.SO . . . I created a .bat file to run the vbs script. THAT didn't run either from an Agent step but it runs when i manually execute it.
SO . . . I ran a little utility that turned the .bat file into a .exe file THAT Operating System step says it succeeds, but it does nothing. However, the executable runs when I manually execute it.
SO . . . I created an SSIS package to run the executable. THAT succeeds, but it does nothing when run from an Agent step but it runs when i manually execute it from the catalog.
I'm really getting frustrated. I hope we can figure this out.
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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 3, 2018 at 2:02 pm
Karen Grube - Thursday, May 3, 2018 1:54 PMI'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.
Tell me about it... Lots of things shouldn't be so hard. I've been alive long enough to have created a rather long list. Fortunately, I've also been alive long enough to learn how to overcome a good chunk of them. Learning, I can highly recommend. Hopefully, we can get you past the Script Task without too much trouble....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 3, 2018 at 2:09 pm
sgmunson - Thursday, May 3, 2018 1:56 PMNone 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply