Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Agent Job running SSIS Package getting "Stuck" Expand / Collapse
Author
Message
Posted Monday, September 8, 2008 6:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 8:02 AM
Points: 242, 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)
Post #565321
Posted Monday, September 8, 2008 6:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #565358
Posted Monday, September 8, 2008 7:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 8:02 AM
Points: 242, 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.
Post #565386
Posted Monday, September 8, 2008 12:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 29, 2011 6:15 PM
Points: 31, 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.
Post #565678
Posted Monday, September 8, 2008 12:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 8:02 AM
Points: 242, 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.
Post #565688
Posted Wednesday, November 5, 2008 1:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 31, 2014 1:00 AM
Points: 3, 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!
Post #597645
Posted Tuesday, October 20, 2009 5:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 16, 2010 7:02 AM
Points: 2, 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.....
Post #805595
Posted Wednesday, October 21, 2009 3:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 16, 2010 7:02 AM
Points: 2, 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.
Post #806327
Posted Wednesday, March 17, 2010 10:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 17, 2010 10:19 PM
Points: 1, 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.
Post #885209
Posted Tuesday, August 28, 2012 1:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:49 PM
Points: 13, Visits: 207
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.


Post #1351204
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse