Thursday, February 01, 2007 12:43 AM
Why are some SSIS files, generated by the Import/Export Data wizard put into the local users temp folder? Why are these not compiled with the package when the solution is built?
Is there some setting I am missing?
This architecture is kind of silly, as the server always needs access to the temp folder on the local machine to run.
How can I get these temp files packaged with the rest of the package and deployed to the server so the server can run independent of the machine I develop the package on?
Thursday, February 01, 2007 12:56 AMI think that's because files generated by the Import/Export Data wizard are supposed to be temporary and isn't really considered part of the package.
What data does your temp files contain? Isn't there a way to extract that data every time the package runs?
Thursday, February 01, 2007 1:05 AMModerator
I think Jon is right. The reason they're put into c:\temp is because they're temporary.
Thursday, February 01, 2007 1:21 AM
I would have thought so too, bu they are an integral part of the package. They contain the truncate table SQL (or if I choose that option, the Drop table SQL). Without this SQL the package will not run.
Thursday, February 01, 2007 2:33 AMModerator
Really? That surprises me. Could you share the package please?
Thursday, February 01, 2007 5:38 AM
Well, I'm not sure how I would do that in this environment. To make matters worse, because I work for a major banking institution, I can't help but think I would be violating some security policy. because the package contains DB names & user IDs.
But, back to the issue at hand....are you saying that if you use the Import/Export Data wizard, and choose to delete existing data (or drop the table first) and choose "optimize for many tables" that it does not create a flat file connection named "PrologueSql" that points to the temp file which contains the Truncate table SQL?
Thursday, February 01, 2007 6:09 AM
Update: It appears that this temporary file behavior only happens when the "Optimize for many Tables" option is selected...
Thursday, February 01, 2007 8:47 AMThe Import/Export wizard created files are stored in the TMP environment variable by default.
To store the Import Export Data wizard files with a checked "Optimize for many tables" setting in a particular directory, create a batch file with the following contents (change the dir name to one you want) and run it.
This approach just changes the TMP location temporarily to a place you'd prefer.
If you don't want to create a batch file, just run the following from Start/Run...
cmd /c set TMP=c:\place_for_files && dtswizard.exe
Thursday, February 01, 2007 10:15 AM
Awesome, thanks jeagd.
Question though...is there a way to tell the wizard to not even use files at all? To just use a SQL task?