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?




    Thursday, February 01, 2007 12:43 AM

All replies

  • 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?
    Thursday, February 01, 2007 12:56 AM
  • I think Jon is right. The reason they're put into c:\temp is because they're temporary.



    Thursday, February 01, 2007 1:05 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.

    Thanks, Jeff

    Thursday, February 01, 2007 1:21 AM
  • Really? That surprises me. Could you share the package please?


    Thursday, February 01, 2007 2:33 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?

    Thanks, Jeff

    Thursday, February 01, 2007 5:38 AM
  • Update: It appears that this temporary file behavior only happens when the "Optimize for many Tables" option is selected...

    Thanks, Jeff

    Thursday, February 01, 2007 6:09 AM
  • 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

    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 8:47 AM
  • Awesome, thanks jeagd.

    Question there a way to tell the wizard to not even use files at all? To just use a SQL task?

    Thanks, Jeff

    Thursday, February 01, 2007 10:15 AM