SSIS package created using Import/Export wizard failing on tmp files.

  • Article

If we try to create a SSIS package from Import/Export wizard with option “Optimize for many tables” its possible that we may run into error messages like

Could not find file 'C:\Documents and Settings\d108967\Local Settings\Temp\1\tmpD7.tmp'.".

The reason for this is the way this SSIS package work. If you open this package in BIDS you would see many File System Connection managers pointing back to files in TEMP directory i.e.

AllowedToFailPrologueSql pointing to C:\Users\runeetv\AppData\Local\Temp\1\tmp6AC1.tmp
InnerPackage.dtsx pointing to C:\Users\runeetv\AppData\Local\Temp\1\InnerPackage.dtsx
PostTransactionSql pointing to C:\Users\runeetv\AppData\Local\Temp\1\tmp6AC2.tmp
CompensatingSql pointing to C:\Users\runeetv\AppData\Local\Temp\1\tmp6AC0.tmp
EpilogueSql pointing to C:\Users\runeetv\AppData\Local\Temp\1\tmp6ABE.tmp
NonTransactableSql pointing to C:\Users\runeetv\AppData\Local\Temp\1\tmp6ABF.tmp
PrologueSql pointing to C:\Users\runeetv\AppData\Local\Temp\1\tmp6ABD.tmp
TableSchema.XML pointing to C:\Users\runeetv\AppData\Local\Temp\1\tmp6ABC.tmp

If TEMP directory is cleared because of any reason, you would get “Can not find file” error messages.

If you create a new package with same tables selected and “Optimize for many tables” selected , new *.tmp files with different name would get created and old package would still fail.

  • The option I see to get this work permanently is by copying these files to a another directory other then TEMP and open your package in BIDS (Business Intelligence development studio) to modify connection string of above file connection managers to your new directory.
  • Second way is by not selecting “Optimize for many tables” . This option would only be useful if you are working with more than 100 tables.

Runeet Vashisht