I have a strange problem. I have several SSIS packages on SQL Server 2005 Developer edition that I execute on schedule. Soon these packages start to fail with the messagse like this:
Executed as user: PETER\Peter Afonin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:13:19 PM Error: 2009-04-16 22:13:20.26 Code: 0xC002F304 Source: NonTransactableSql Execute SQL Task Description: An error occurred with the following error message: "Could not find file 'C:\Users\Peter Afonin\AppData\Local\Temp\tmpD49E.tmp'.". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:13:19 PM Finished: 10:13:20 PM Elapsed: 0.811 seconds. The package execution failed. The step failed.
I'd realized that the packages require some tmp files in the Temp folder for execution. The problems here are:
1. This is too ridiculous to be true. The execution of the packages cannot rely on the tmp files that are automatically deleted on every disk clean-up. Now I have to backup my junk files folder and restore these tmp files after each clean-up. And I couldn't find any options in SQL Server to change the location for these files.
2. Although I always have this problem on any computer and any operating system, I couldn't find much information on this in Google or newsgroups, so apparently this is not a common problem.
This makes me think that I'm doing something wrong, but I cannot figure out what exactly.
I would appreciate your help on this. Thanks.