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.