Okay, here is my situation:
I have created an SSIS package to run PSCP.exe in order to pull a specific file from a secure FTP site, then it creates a staging table and imports any files in the local import folder, then runs a stored procedure to merge the staging and destination tables, and then drops the staging table. All of this works like a charm from BIDS or manual launching the 32 bit DTEXEC.exe from the command line. But, as soon as I run this through a SQL Agent job it doesn't work. By not work I don't mean returns an error, or any thing else that would give me a direction, I mean it starts, I see DTEXEC launch, and then it does nothing. It does not complete, despite how long I leave it run (the normal package takes around 15 seconds, and I have left this run for nearly 24 hours). The text I am running in my job (of type Operating System (CmdExec)) is the exact same text that executes successfully from the run menu. I have tried using my typical domain account that is also a local and database admin (and the account SQL Agent is running on), and I have tried using my domain account, which is also a local admin, but to no avail. I have verified security on the concerned folder, but these are both local admin accounts, and the folders are local.
More Info: I am running this on Windows Server 2003 Standard x64 Edition, with Microsoft SQL Server 2005 - 9.00.3239.00 (X64, Build 3790: Service Pack 2). The exact statement I am using to try and launch my package is
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /DTS "\MSDB\Vendor Data Loads\ADP Employee List" /SERVER DCPBI /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
, though I have also tried verbose mode without any change.
Does anyone have any thoughts on how I should proceed?