March 5, 2009 at 1:04 pm
So I've got these SSIS packages that run fine in BIDS, but fail when we try to run them from a SQL Agent job. A bit of background:
These packages (and agent job) all work fine on our production environment, but when they were copied down to a test server, we started having problems. Specifically, when trying to execute the packages with a SQL Agent job using a service account, the packages fail on an execute process step which is trying to execute a batch file. In BIDS this all works fine. But when the agent tries to run the package, it fails on this step, saying that it can't find the file in the directory indicated. But the file is there, in the right directory, with the correct name.
- The service account is set up as a domain admin on the box. I have verified that I can log in using the service account, and access the batch files directly in the appropriate directory
- This is NOT a mapped network drive. This is a local (D) drive on the machine where the agent and packages reside
- Other steps in the package that access files on the same drive (for example a flat file connector that is used to pull source data from a flat file) work fine, but the execute process step fails.
- Also there is logging set up (using the logging to a text file provider) which also does not work when running from the Agent.
- The service account, sql server install, operating system, etc. is all configured the same way on Prod and Test servers.
Again, all works fine when running in BIDS, all works fine running the agent job on Prod, just running on Test with the agent where we have this problem.
Here is the relevant error messages returned in the Agent history when the job fails (both for the logging error and the execute process task error):
Executed as user: AWW\BPCPR. ...ackage Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:56:25 PM Error: 2009-03-04 14:56:25.79 Code: 0xC001404B Source: VideoProductCSVFileLoad Log provider "SSIS log provider for Text files" Description: The SSIS logging provider has failed to open the log. Error code: 0x80070003. The system cannot find the path specified. End Error Error: 2009-03-04 14:56:25.92 Code: 0xC0029153 Source: VIDEO BACKLOG CLEANUP Execute Process Task Description: File/Process "VIDEO_BACKLOG_FILE_CLEAN_UP.BAT" does not exist in directory "D:\SSIS\BAT FILES". End Error Error: 2009-03-04 14:56:25.92 Code: 0xC0024107 Source: VIDEO BACKLOG CLEANUP Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:56:25 PM Finished: 2:56:25 PM Elapsed: 0.734 seconds. The package execution failed. The step failed.
Has anyone seen this type of problem before? Everything I've found thus far online has related to situations where someone was trying to use a mapped network drive reference. Again, that is not the case here.
Any ideas?
Thanks
March 5, 2009 at 8:17 pm
Try removing the space from the batch file folder name. I know it sounds strange, but I ran into this with a package a while back (mine was a remote folder and not local, and I was trying to write to the directory) and removing the space from one of the directory names cured my ills.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 6, 2009 at 8:36 am
Thanks for the response Tim. I'd rather not mess with the directory names, because at this point I don't know what the impact of such a change would be on other apps. Also, it doesn't explain why this all works in the prod environment, where the directory name has the space...but again I appreciate the suggestion.
March 6, 2009 at 9:04 am
Issue resolved. Even though this is supposedly not a network share, I was able to get the package to run from the agent by replacing the drive letter reference with "\\servername\D$\". Go figure. Why this was necessary on the test server but not the prod server, no idea...
Now if I can just figure out all the other errors that the agent job now throws, now that it got past that first error! I have a bad feeling that this test server is not as much of an "exact mirror" of prod as I have been led to believe...
:sick:
March 6, 2009 at 9:25 am
Frank The Tank (3/6/2009)
I have a bad feeling that this test server is not as much of an "exact mirror" of prod as I have been led to believe...:sick:
In my experience, they never are...
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply