I’ve recently spent a day of so of my life doing something which I did in minutes using DTS (And I’m sure on SSIS in the past??). I selected export data from a database in sql 2005 and chose my destination database and a bunch of tables. At the end I select save as SSIS package; everything runs fine.
I can exit the sql management studio, run the package again– all ok.
I then log out, log back in, run the package, and I’m swamped with errors about missing tmp files.
It turns out that the ssis wizard, dumps critical parts of the ssis package (table schema and the like) out into temp files (Only when you tick optimize for multiple tables- whcih I had to in order to be able to use transactions), which on our system gets wiped on logout- so when you come back, the package is crippled.
I found a solution on BigResource.com from user jaegd;
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.
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
This has fixed it! Such a relief- I set the env var tmp to c:\scripts\SSISFiles then generate the SSIS package, and save it to the file system in c:\scripts\ with no sensitive data embedded, then fire it off from the command line, supplying the connection strings.
dtexec.exe /FILE "C:\scripts\MySSISPackage.dtsx" /CONNECTION DestinationConnection;"\"Data Source=SiteDB\SQLEXPRESS;User ID=sync-user;Password=abc123;Initial Catalog=Chinook;Provider=SQLNCLI;Auto Translate=false;\"" /CONNECTION SourceConnection;"\"Data Source=CentralSQL;User ID=sync-user;Password=abc123;Initial Catalog=ProductDataStore;Provider=SQLNCLI;Auto Translate=false;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT >Log.txt
This way everything is kept nicely grouped together. Thought I would blog it for future me, and to prevent anyone else shaving 3 years off their life with stress.