September 8, 2008 at 6:15 am
Help!!!!
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?
Thanks,
Tim (MentalWhiteNoise)
September 8, 2008 at 6:58 am
To start with, turn on package logging to see where exactly it is getting stuck. BIDS is 32 bit, so you may be having a problem with the job running in the 64 bit runtime. You should find another program files folder with the 32 bit DTExec runtime in it - this is a good way to determine if you have a 64 bit specific problem.
September 8, 2008 at 7:31 am
Thank you for the response.
I have actually boiled the problem down to the executable that I am using to pull the file: PSCP.exe. If I call this exe from a command line, I have no problems. If I call it from any scheduled task (SQL or Windows), it locks without error. If I call a batch file containing a call to this executable it works, if I schedule the batch file it locks. I am currently testing WinSCP, and I am hoping that this executable, as it is specifically compiled for a 64 bit windows os, will resolve my issue.
September 8, 2008 at 12:28 pm
i had this issues before. what i did since this only works on the CMD shell is create a stored proc that will use xp_cmdshell to call the DTEXEC command.
September 8, 2008 at 12:36 pm
I think I got it figured out... the issue has to do with the "fingerprint" for sftp (scp). For some reason, even if I set the SQL Server job to use my login, the security fingerpring needs to be in the cache of the sql server agent service user. I am not sure yet if this is a temporary or permanent solution... I need to do some more research on sftp security.
November 5, 2008 at 1:02 pm
I am using WinSCP and having the same problem. It is actually a SSIS package which I can execute on its own, it transfers the file successfully. But the same when executed through a stored procedure, it fails. I modified the stored procedure to print the command and I can take that and execute it from the cmd prompt without any problems. Any ideas or help is greatly appreciated! Thanks!
October 20, 2009 at 5:22 am
Hi All,
Timothy did you find a solution for an issue described in this post?
I have now the same problem and I can not get a solution for it.
Please help if someone knows how to solve it.....
October 21, 2009 at 3:13 am
As Timothy wrote in his last post the issue why SQL Server is frizzing without doing anything is that the fingerprint of the Unix server was not cached in the registry for the user for whom SQL Server is running.
To omit this problem one thing need to be done:
Add to registry for user for whom SQL Server is running (or SQL Server Agent if you wont to use PSCP in SQL JOB)
KEY in a directory:
\Software\SimonTatham\PuTTY\SshHostKeysand looking like this:
name: rsa2@22:xxx.xxx.xxx
xxx.xxx.xxx - IP address of a Unix server from/where you wont to send a files
type: String
value: fingerprint of Unix server (if don't know from where you could take such fingerprint, you should start PUTTY and try to login to the Unix server. (of course if didn't make it before)). During logging attempt a fingerprint of a server is being added to a registry. Now you should look to registry and find in a directory HKEY_CURRENT_USER\Software\SimonTatham\PuTTY\SshHostKeys\ key that name look like this rsa2@22:xxx.xxx.xxx.
Value of this key need to be copied to the key created by you for SQL server user.
I have hope that it will help if you will have similar problem.
March 17, 2010 at 10:25 pm
I had the same (or similar) problem. Here is the troubleshooting and what fix it for me:
1. Check the job history if the history is not logged check the error log for SQL Agent.
2. For me there were recurring errors complaining about not being able to find the server. So basically the package was executed (and I verified that) but it was not able to log the history.
3. After several hours I noticed that I had defined an alias with an old IP address. Once I corrected it everything worked fine.
August 28, 2012 at 1:16 pm
Here's a simpler fix. A sister program to pscp is psftp, which is interactive. The first time you run it from a command window, it gives you the fingerprint and asks if you want to cache it. I logged in with the SQL Server Agent account, ran psftp, and now my job runs with no problem.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply