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


Automating Excel via SSIS and SQL Agent


Automating Excel via SSIS and SQL Agent

Author
Message
keval.bhatt
keval.bhatt
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2266 Visits: 7821
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);




  • 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

  • keval.bhatt
    keval.bhatt
    Forum Newbie
    Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

    Group: General Forum Members
    Points: 4 Visits: 23
    Yes, our DBA logged on and initialised Excel. We worked through that last week.
    mister.magoo
    mister.magoo
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2266 Visits: 7821
    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);




  • 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

  • keval.bhatt
    keval.bhatt
    Forum Newbie
    Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

    Group: General Forum Members
    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.
    mister.magoo
    mister.magoo
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2266 Visits: 7821
    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);




  • 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

  • keval.bhatt
    keval.bhatt
    Forum Newbie
    Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

    Group: General Forum Members
    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.
    mister.magoo
    mister.magoo
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2266 Visits: 7821
    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);




  • 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

  • Ed Zann
    Ed Zann
    Mr or Mrs. 500
    Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)

    Group: General Forum Members
    Points: 505 Visits: 1391
    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
    CozyRoc
    Ten Centuries
    Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

    Group: General Forum Members
    Points: 1150 Visits: 2235
    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/


    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