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?
I 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?
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?
The 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