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\
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.
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\
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.
Steve Hogg
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.
ASKER
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.
and what are those tasks doing?
ASKER
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.
That sounds like Data Flow tasks. Can you post a screen shot? Or the package?
ASKER
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.
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.
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.
ASKER
OK, thanks, I'll do this tonight.
ASKER
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
Sync-i90partner-v3.zip
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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\
Where do you see this? How could it happen that this was created? What should I do to get rid of it?
Thank you.
Where do you see this? How could it happen that this was created? What should I do to get rid of it?
Thank you.
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'
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
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'
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
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
When you run the wizard with the "Transaction" option, it creates a very complicated package.
ASKER
I did not choose this option. The only option I'd choosen was "Optimize for many tables" or something like this.
Oh, that is it. It creates a Transaction when you choose Optimize for many tables.
ASKER
OK, thanks. I'll try to fix this tonight and see how it goes.
ASKER
Sorry, couldn't check anything last night. I'll do this today for sure.
ASKER
I've checked this - yes, they are all pointing to the files like this:
C:\Users\Peter Afonin\AppData\Local\Temp\
C:\Users\Peter Afonin\AppData\Local\Temp\
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.
C:\Users\Peter Afonin\AppData\Local\Temp\
C:\Users\Peter Afonin\AppData\Local\Temp\
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.
Did following Hogzillas advice (don't choose optimize for many tables) stop this happenning?
ASKER
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.
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)
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.
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.
ASKER
Thank you.
Yes, by editing the package this problem can be eliminated. It's just strange that wizard creates such bizarre package by default.
Yes, by editing the package this problem can be eliminated. It's just strange that wizard creates such bizarre package by default.