Evil Kraig F (8/23/2012)
Two things are going on here, and they're not entirely independent but a few questions.I assume you originally built this package from your local system, but the server is not local and is on the network somewhere. With that assumption, I'll keep going. If that's incorrect, please fill us in on the details.
Your SQLAgent on that server is probably set to localhost somethingorother instead of an actual NT network account. Thus, it's attempting to do an sa login and failing since the password isn't in the connection of the package you built. Secondly, the temp file it's looking for is being looked for locally on the server, not your system, so it can't function from the server.
What I would recommend doing is taking the dtsx package you've built, opening it up in BIDS, and getting the data source set to a more neutral location, such as a folder on a file system somewhere on the network. Next, if you're an admin on the server, get into the SQLAgent settings in the Services (Computer-Manage) and find out what login it's pretending to be.
Hi Kraig!
Actually - I created the job from an RDP session over to one of the servers involved. I attached to the other instance inside of that server's instance, and created the package from the export wizard.
I just verified that both servers have the SQL Agent using this one account 'SQLAdmin'.
Sorry for the delay in responding to this, but hopefully I can come up with a fix for it soon.
P.S. I do not have BIDS on this system, which is why I am trying to set the package up in either SQL Server or from the file system as the package store is not available. :-/