SSIS, BIDS and Temporary files
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?
Thanks,
Jeff
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?
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.
Thanks, Jeff
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?
Thanks, Jeff
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.
set TMP=c:\place_for_files
dtswizard.exe
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
