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.