Automating Excel via SSIS and SQL Agent

Question

Automating Excel via SSIS and SQL Agent

keval.bhatt

Old Hand

Points: 314
More actions
February 20, 2013 at 11:03 am

#270646

Hi
I have encountered an issue on a project that I'm rather struggling with and I'm hoping someone has encountered something similar and may be able to help.
I am trying to produce a series of Excel workbooks.containing multiple sheets, showing various sets of data. There is one workbook per region, but the sheets have different content, including flat data taken from an SQL database, pivot-tables, charts. The workbooks are distributed to people in the business who have to provide updates, but only within certain cells.
To do this, I have gone down the SSIS route (we use SQL Server 2005, on a virtual box running MS Server 2008), using data flow tasks within for...next loops to export the data from the database into text files. Once a set of files is created, an Execute Process task executes a VBScript routine that opens an Excel template file, pulls in the relevant files into their respective sheets, saves a uniquely identified file and calls a process within the template to perform some further processing (consolidating data into one sheet and refreshing pivot tables based on the consolidated sheet).
I've gone down this road due to various reasons:
- VBScript due to an inability to access the Excel Interop assembly through BIDS/Visual Studio
- Exporting to text files as some datasets are exceeding 65000 records
- Using a template Excel file as it contains code to restrict certain cells, update the consolidated data once amendments are made, log changes to easily identify updates and export them for import back into the database
This entire process runs through successfully if I run it through BIDS 2005. However, if I run the SSIS package via the SQL Agent, the job hangs when running the VBScript file, at the point where the Excel instance is opened (as if there is a dialog box appearing when opening Excel). The template, VBScript and export files all reside on the network and the agent has access to those locations. The export files get created and the script starts to run, but hangs once Excel starts. Our DBA has tried logging into the server as the SQL Agent and no dialog boxes or warning messages are appearing. The job doesn't error out, and there is no indication that there is a problem.
We would like it running via the SQL Agent to speed up the processing and to ensure that it can be triggered remotely without needing to have access to the server.
If anyone has any suggestions on what might be happening, I'd be very grateful.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Have you tried not just logging on as the SQL Agent user, but also opening excel to initialise the office system for that user?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

keval.bhatt Old Hand Points: 314 More actions · Answer 2

Yes, our DBA logged on and initialised Excel. We worked through that last week.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

keval.bhatt (2/21/2013)
Yes, our DBA logged on and initialised Excel. We worked through that last week...

Our DBA has tried logging into the server as the SQL Agent and no dialog boxes or warning messages are appearing.

Sorry, I left my mind-reading head at home.

Office automation is not supported in a server environment. Find another way.

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

keval.bhatt Old Hand Points: 314 More actions · Answer 4

Thanks for your responses, and apologies if my 'we worked through that last week' sounded snippy - that wasn't my intention.

Would you have any suggestions on other ways to go through this process? My only other thought at the moment would be to create all the text file outputs via SSIS and SQL Agent and initiating the Excel automation from a front-end tool on the local machine. I have avoided this as we are trying to rationalise our dependence on in-house tools on local machines.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

If you are using OpenXml style workbooks (xlsx) then I would suggest using the OpenXml library to create them, or if that proves too difficult, you might investigate the structure of your templates to see if you can easily just produce the data as xml and "build" the spreadsheets that way...

What do I mean? well, if you unzip an xlsx file, you get some folders with XML files in. You can investigate these to find the ones that you want to populate and then generate those XML files in SSIS, drop them into the folder structure and then zip the folders back up to make a new xlsx.

It's not for the faint of heart though.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

keval.bhatt Old Hand Points: 314 More actions · Answer 6

Oh boy, I have just opened up an xlsx file in 7zip and had a look at the xml. I think at this stage, any of that is *way* beyond my skills right now. I think for now I'll stick with triggering the script locally and producing the files that way for now. I may revisit the XML solution a bit further down the line.

Thanks for your help though, it's been greatly appreciated.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Maybe someone else can help with the server side excel - I know from past experience there is a reason MS don't support it :crazy: so I steer clear myself...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 8

You might want to try using the PSExec utility to invoke Excel on another workstation and do that piece of the processing after the data files are created. I use PSExec in SQL Agent jobs to invoke import utilities on other boxes once their data files have been created. It has worked like a charm. Not sure how Excel will do with it though.

Couple things. . .

There is a license dialog that must be answered interactively the first time PSExec is run. Do this before you try to use it in a job.

I have a situation where one of my import utilities won't work if I call the EXE directly from PSExec. So I have PSExec invoke a scheduled task that runs the import on the remote server. Kinda like the game Mousetrap, more moving parts than one would like, but it works.

COZYROC One Orange Chip Points: 28499 More actions · Answer 9

Excel automation from SQL Job Agent will not work because the Excel automation is based on running the Excel application itself. The Excel application is desktop application which requires full user profile. Here is where the problem lies. The SQL Job Agent runs lightweight partial (non-full) user profile accounts which means you can expect many problems trying to invoke Excel from the job agent.

I would recommend you create a solution which doesn't depend on Excel automation.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

smecharrafie Ten Centuries Points: 1135 More actions · Answer 10

Dears,

I have faced related scenarios in that context for which I have managed to find a workaround, however until recently process started giving me a hard time.

My flow consists of an SQL Server Agent Job which triggers packages the contain VB Script task component to open and apply specific formatting to weekly generated Excel files. The original platform was Windows Server 2003 and all was well back then, however after the upgrade to Windows Server 2008 the job started to fail on execution noting that execution of the package from within BIDS would run with no issues. As per the research this is due to the fact that automation/unattended execution of Microsoft Office is not fully supported on Windows Server 2008, yet the creation of the 'Desktop' folder under C:\Windows\SysWow64\config\systemprofile solved the issue back then.

A few months later I faced another error when the job was triggered and I found a workaround by calling the packages in the job in 32 bit mode via command line (DTExec Util) and this worked well for around a year now... until last week upon execution, the job seems to go on forever without doing anything. Excel.exe does actually open up in the task manager processes, however the write functionality never starts maybe due to user restrictions??

Note that no windows updates or other changes have been made during last couple of weeks, I shall try to modify user permissions however not counting on that as the Excel.exe process is being triggered by Administrator user. Any suggestions are appreciated.

Regards,

Sam

Automating Excel via SSIS and SQL Agent

Cookies on SQLServerCentral