SSIS, BIDS and Temporary files

Question

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

 

Answer 1

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?

Answer 2

I think Jon is right. The reason they're put into c:\temp is because they're temporary.

-Jamie

 

Answer 3

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

Answer 4

Really? That surprises me. Could you share the package please?

 

Answer 5

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

Answer 6

Update: It appears that this temporary file behavior only happens when the "Optimize for many Tables" option is selected...

Thanks, Jeff

Answer 7

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

Answer 8

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?

Thanks, Jeff