SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Agent Job running SSIS Package getting "Stuck"


SQL Agent Job running SSIS Package getting "Stuck"

Author
Message
Timothy J Hartford
Timothy J Hartford
SSC-Addicted
SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)

Group: General Forum Members
Points: 410 Visits: 502
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)
Michael Earl-395764
Michael Earl-395764
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4981 Visits: 23078
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.
Timothy J Hartford
Timothy J Hartford
SSC-Addicted
SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)

Group: General Forum Members
Points: 410 Visits: 502
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.
hmaharaj
hmaharaj
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 126
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.
Timothy J Hartford
Timothy J Hartford
SSC-Addicted
SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)

Group: General Forum Members
Points: 410 Visits: 502
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.
s98ssr
s98ssr
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 44
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!
majon
majon
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 19
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.....
majon
majon
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 19
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\SshHostKeys\
and 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.
hetcorp
hetcorp
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 8
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.
kduffy
kduffy
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 218
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search