SSIS execution asks for temp files

Hello,

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.
ehitekAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
HoggZillaConnect With a Mentor Commented:
Open each of your File Connection Managers and check the ConnectionString property. Are all of the files pointing to an accessible path, preferably a fully resolved UNC path. http://www.uwplatt.edu/oit/terms/uncpath.html
It seems the Execute SQL Task, NonTransactableSQL is set to the source type = File Connection. Open the SQL Task and see which FileConnection it is using. Put the file it is using into a shared folder and change the File Connection Manager connection string to a UNC path to the new file location.
Let me know.
0
 
HoggZillaCommented:
Check your packages for any Data Flow Tasks and the properties, BufferTempStoragePath. If the property is blank, provide a path that is easily accessible to the server.
0
 
ehitekAuthor Commented:
There are no dataflow tasks. I've created these packages by importing the data from the databases on the remote server and saving them as packages. Now if I edit these packages with the Visual Studio, there is nothing under the Data Flow tab. All tasks are under the Control Flow tab.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nmcdermaidCommented:
and what are those tasks doing?
0
 
ehitekAuthor Commented:
They are importing data from the tables on the remote server to the tables on the server on my PC. I have several tasks enabled - one for each database.
0
 
HoggZillaCommented:
That sounds like Data Flow tasks. Can you post a screen shot? Or the package?
0
 
ehitekAuthor Commented:
Yes, I'll do it tonight.

But how is this related to the issue? If I have these tmp files in the Temp folder, then I have no problem executing packages. Sorry, I'm not an expert in SSIS, so I'm not sure how this should work.
0
 
HoggZillaCommented:
To be very honest, it does not make sense to me either. I hope I can figure it out for you if I have the package to look at.
0
 
ehitekAuthor Commented:
OK, thanks, I'll do this tonight.
0
 
ehitekAuthor Commented:
I've attheched the package file. Please rename Sync_i90partner_v3.TXT to Sync_i90partner_v3.dtsx, since the dtsx extension is not allowed. Thanks.
Sync-i90partner-v3.zip
0
 
nmcdermaidConnect With a Mentor Commented:
You have an execute package task which is executing a package at this location:
C:\Users\Peter Afonin\AppData\Local\Temp\909a6e49-ada7-4396-b336-d8450f4cfcb9.dtsx
Did you do that on purpose? Whats the reason for that? I can only assume your problem is related to this.
 
0
 
ehitekAuthor Commented:
I have no idea where this came from. As I said before, I was just importing the data from the remote server and saving it as a package in the file system. All packages are stored in the My Documents folder. I know nothing about C:\Users\Peter Afonin\AppData\Local\Temp\909a6e49-ada7-4396-b336-d8450f4cfcb9.dtsx.

Where do you see this? How could it happen that this was created? What should I do to get rid of it?

Thank you.
0
 
nmcdermaidCommented:
Actually all of the connection managers (at the bottom) are pointing at files/objects in the temp directory.
Can you clarify how you created this package? Maybe it was built with the wizard (I don't like wizards so I'm not familiar with it)
For example, what are you expecting for the 'AllowedToFailPrologueSQL' connection manager, as currently it is picking its SQL up from a text file in the temp folder.
This could certainly be valid - a master package could be creating those files but like your original comment says - thats ridiculous.
I'm guessing this was made with some kind of wizard or is part of some bigger picture. Now you have to clarify what the bigger picture is.
 

FullPackage.jpg
TempFile.jpg
0
 
nmcdermaidCommented:
I'm guessing when you use the wizard to move data around, it creates that particularly named package in that particular folder. I would have thought that the wizard would be more robust but it seems not.
So all of those connection managers etc. have been set up by the wizard.
Perhaps Hogzilla can shed some more light on this as I am not too familiar with the wizard.
 
 
0
 
ehitekAuthor Commented:
OK, thank you, I'll try all this tonight.

Yes, it was created by wizard. I just right-click on the database in the Management Studio, go to Tasks - Import data, then go through the steps. At the end I choose the option to save these steps as an SSIS package in the File System. Then I schedule a job to execute this package on schedule.

Since I'm not an expert in SSIS, to me this was an easiest and the most intuitive way.
0
 
HoggZillaCommented:
When you run the wizard with the "Transaction" option, it creates a very complicated package.
0
 
ehitekAuthor Commented:
I did not choose this option. The only option I'd choosen was "Optimize for many tables" or something like this.
0
 
HoggZillaCommented:
Oh, that is it. It creates a Transaction when you choose Optimize for many tables.
0
 
ehitekAuthor Commented:
OK, thanks. I'll try to fix this tonight and see how it goes.
0
 
ehitekAuthor Commented:
Sorry, couldn't check anything last night. I'll do this today for sure.
0
 
ehitekAuthor Commented:
I've checked this - yes, they are all pointing to the files like this:

C:\Users\Peter Afonin\AppData\Local\Temp\tmp8488.tmp
C:\Users\Peter Afonin\AppData\Local\Temp\909a6e49-ada7-4396-b336-d8450f4cfcb9.dtsx

Now the last question - is there a way to avoid this in the future if I'm using the wizard? There is only one different option I can select - to store the package in the SQL Server rather than in the File system. Wil this change anything? Or I have to manually modify each package?

Thank you.
0
 
nmcdermaidCommented:
Did following Hogzillas advice (don't choose optimize for many tables) stop this happenning?
0
 
ehitekAuthor Commented:
I haven't tried it yet, but I'm sure it will. Now that I know where these tmp files are coming from, I can move them to the different safe forder and reset the connections. But I wonder whether I have to do this every time I create a new package using wizard, or there is a way to avoid it.
0
 
nmcdermaidCommented:
Sorry I can't help you there. You could try saving it to a public area (like C:\) rather than My Documents (which is a user specific area)
0
 
JMD63Commented:
Hi ehitek,
I had the same problem as you did.  I had used the import data wizard to create a package and it would not execute the correctly on subsequent runs.  So I edited the package by adding two executeSQL tasks. The first one to delete the destination database and the second one to create the databas again.
Of course you have to maked sure you specify the correct connection (meaning a connection to the server but not using the database you want to drop and create) and the control flow should then be passed to your NonTransactableSQL task,
Hope it works for you too.
0
 
ehitekAuthor Commented:
Thank you.

Yes, by editing the package this problem can be eliminated. It's just strange that wizard creates such bizarre package by default.
0
All Courses

From novice to tech pro — start learning today.