Solved

Unexpected results running SSIS package, using execute process task, in SQL Server Agent (2008R2)

Posted on 2014-09-22
11
1,170 Views
Last Modified: 2016-02-11
I've been banging my head against a brick wall for what seems like forever.  This is what I have done :

Written a VB.Net console application (lets call it myvb.exe) which accepts a number of parameters and creates an Excel 2010 workbook
Created an SSIS package which iterates a number of records in SQL Server and executes myvb.exe

This package works successfully when I run from BIDS.  It works successfully when I publish the package into SSIS.  It works successfully when I run from a command prompt using "DTExec.exe".  What it doesn't do, is run as a scheduled job using SQL Server Agent.  Well, strictly speaking, it does run, I get no errors, but the expected Excel files do not get created.  Task manager shows me that Excel instances are being created/destroyed on the fly.

The agent is running under my logged on account, so there should be no issue there (hopefully).  It is running on the same machine from which I designed all the elements, so there is no issue with a different setup.  It doesn't use UNC path, the application is run directly from the c:\ drive.  It is set to run using 32-bit runtime, hopefully that eliminates an issue we've come across with Office on the production server.  I've tried turning on package logging, but it tells me little else other than when each iteration starts and finishes.  I've added scripts tasks to write what is passed to the exe to a log file, all commands look OK.

I'm at a loss.  I'm obviously missing something very basic, but I'm b***** sure I can't see it.  Any guidance would be appreciated.

Thanks in advance.
0
Comment
Question by:AHJ2008
  • 7
  • 3
11 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40338583
The agent is running under your account but what about the job? It's the owner of the job that matters not the SQL Server Agent.
You can also run a SQL Profiler to verify if the job is at least doing something on the database.
0
 

Author Comment

by:AHJ2008
ID: 40338673
Hi Vitor, thanks for responding.

I've run a couple of queries on sysjobs and sysssispackages and they appear to be owned by my logged on account.

Few things maybe I should have mentioned :

I'm running SQL Server 2008 Developer Edition (R2) - don't know if there are limitations to the agent
The exe is compiled using Visual Studio 2010 as 32-bit and references Office 2010 64-bit assemblies
The project is configured "Run64BitRuntime = False"
The package is configured "Use 32 bit runtime"

Thanks again

Julie
0
 

Author Comment

by:AHJ2008
ID: 40338981
Ok, done some further testing and introduced debugging in the vb.net application, and discovered that when running the exe through the SQL job, it can recognize the folder and access it, locate the file required, but can not create a new workbook based on it.  I'm assuming it is an Office automation issue.  I found the following :

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.

Do I assume I've hit the unstable behaviour mentioned above?  I have references to the Office assemblies in the visual studio project and use early binding.

Quick question, would the same be true if I use late binding and object references?  Is that still considered Office Automation? I'd rather avoid a lot re-programming if the principal is flawed.

Unless I've totally misunderstood ....
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40339428
Are all your workbooks the same before you write data into them? At least, are your workbooks always one of a number of pre-known formats? If so, then create blank copies of each type of workbook ahead of time and keep them in a templates directory.

At run time don't create a new workbook - instead, copy the appropriate workbook for the task you're about to carry out to the working directory (leaving the original in the templates directory).

This way you never create a new workbook in the job.

hth

Mike
0
 

Author Comment

by:AHJ2008
ID: 40339516
Thanks Mike

The workbook template is the same.  I was creating a new workbook based on the template, changing the query definition within each sheet on the fly, and saving as a new workbook.  

OK, I understand what your saying, copy the master to a new file, change those query definitions, and save.  You think that gets round the Office Automation limitation because we're performing file manipulation instead?  It will require some re-programming, and time, but I'll give a try.

Where there's a will there's a way ...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 16

Expert Comment

by:DcpKing
ID: 40339631
Yes. It seems, from what you said,  that it was the creation part that was failing. Hopefully that was the right diagnosis!

Mike
0
 

Assisted Solution

by:AHJ2008
AHJ2008 earned 0 total points
ID: 40339725
Hi Mike

I've managed to perform some further testing, and I've found that the file gets copied successfully, it just can't open it.  The error message is :
Microsoft Excel cannot access the file '<folder>\<name>.xlsx'. There are several possible reasons:

The file name or path does not exist.
The file is being used by another program.
The workbook you are trying to save has the same name as a currently open workbook.
Unfortunately, to all intents and purposes, this is the same message as when I was creating a new workbook based on the template.

Still an automation issue then I assume?
0
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 500 total points
ID: 40339995
Probably. Can you not write into an existing workbook with the Excel connector in SSIS?
0
 

Author Comment

by:AHJ2008
ID: 40341055
Yeah, that's how I arrived at this party, I just wasn't happy with the formatting and I couldn't get it to work with *.xlsx.

Plan D then ...

Thanks to all for your suggestions

Julie
0
 

Accepted Solution

by:
AHJ2008 earned 0 total points
ID: 40341774
Just as a postscript, I can schedule the package to run using DTExec and Windows Task Scheduler ... another ball to keep in the air, but as long as one scheduler or another can generate the workbooks, I'm happy.  It's not been all for nothing!

Thanks again to all

Julie
0
 

Author Closing Comment

by:AHJ2008
ID: 40349531
Although the suggested tip did not resolve the problem, it confirmed I could not achieve what I wanted using the method I had been employing.  This helped lead me to finding an alternative way getting the desired results.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now