Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automating Excel via SSIS and SQL Agent Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 11:36 PM
Points: 4, Visits: 23
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.
Post #1422219
Posted Wednesday, February 20, 2013 12:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:19 PM
Points: 1,805, Visits: 5,864
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1422239
    Posted Thursday, February 21, 2013 1:43 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, February 21, 2013 11:36 PM
    Points: 4, Visits: 23
    Yes, our DBA logged on and initialised Excel. We worked through that last week.
    Post #1422435
    Posted Thursday, February 21, 2013 2:26 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 6:19 PM
    Points: 1,805, Visits: 5,864
    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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1422452
    Posted Thursday, February 21, 2013 2:38 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, February 21, 2013 11:36 PM
    Points: 4, Visits: 23
    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.
    Post #1422457
    Posted Thursday, February 21, 2013 3:21 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 6:19 PM
    Points: 1,805, Visits: 5,864
    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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1422474
    Posted Thursday, February 21, 2013 7:49 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, February 21, 2013 11:36 PM
    Points: 4, Visits: 23
    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.
    Post #1422594
    Posted Thursday, February 21, 2013 7:52 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 6:19 PM
    Points: 1,805, Visits: 5,864
    Maybe someone else can help with the server side excel - I know from past experience there is a reason MS don't support it so I steer clear myself...

    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1422597
    Posted Thursday, February 21, 2013 12:48 PM


    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Wednesday, November 19, 2014 11:50 AM
    Points: 487, Visits: 1,239
    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.



    Post #1422759
    Posted Sunday, March 3, 2013 9:52 AM


    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Sunday, September 14, 2014 3:17 PM
    Points: 1,118, Visits: 2,224
    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/

    Post #1425969
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse