June 23, 2008 at 12:51 pm
We have various overnight processes that run though batch scripts. These scripts pull data from other locations into flat files for the SSIS package to read. Then we call DTExec to run the package. Often, the package will not even start, no SSIS logging occurs, the validation process doesn't start, and it just hangs for an hour before the batch times out (DTExec never times out).
The setup is we have a server running the script, which calls the SSIS package on different SQL Server (2005 SP2 Standard Edition) through the DTExec command.
This is an example of what our batch log shows:
Running job: SSIS-Job1
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 3:06:23 AM
Timeout!
Logging ended at: Tue May 20 04:05:07 2008
This issue has been occurring for a few months now, and there is no consistency at all, we are running multiple iterations of this script for various projects and sometimes one fails, sometimes another fails, sometimes none fail, etc. It doesn't happen at a specific time or during any other processes, etc.
After the script sends the timeout, the server is still showing an instance of DTExec running in task manager.
I've found posts about slow packages but none about non-running packages.
Has anyone ever seen this? Any suggestions what else we could be looking for?
June 24, 2008 at 9:45 am
Has no one ever seen this behavior before? Any ideas would be most appreciated.
June 25, 2008 at 12:48 am
Andrew,
I know you are saying that it hangs before it starts, but from your log it shows that it started...
Maybe you should consider package logging, and have a look at where in the process the bottleneck is, this way at least you know where to start looking
~PD
June 25, 2008 at 7:22 am
The package has full logging turned on with manual logging added in as well. The problem is we don't get a single line logged when this issue occurs. It appears that somewhere between the DTEXEC call and the package validation something is failing. Unfortunately there isn't much documentation about what dtexec does. We can't even seem to figure out if the package runs on the client or the server.
July 9, 2008 at 7:19 am
I've seen this problem too, but can't find the root cause. Anyone out there have any ideas or why a package appears to start but never really executes?
July 9, 2008 at 7:26 am
Have you added logging to the batch file, in that can you redirect it's output to a text file to ensure that calls are being made. In addition what happens when you run this manually?
July 9, 2008 at 7:32 am
The log from my original post is the output from the Batch Script.
As this is an intermittent issue, it's works manually most of the time (running DTExec from a command line) but even that has occasionally failed.
July 15, 2008 at 8:54 am
Occasionally when we run the package manually we get the same result, but most of the time it runs without an issue. Doesn't seem to be related to any network issues or issues with the virtual machine executing the package, everything runs "normally". When it does fail, nothing gets logged anywhere (we've tried logging to a text file) and nothing shows in the event log on the server as well. One thing does look weird. The DTEXEC.exe process gets hung and it's impossible to kill it through task manager. We wind up having to reboot to clear it.
I share Andrew's frustration...doesn't seem to be a reason why the package shouldn't start in the first place. Anyone ever have this happen or have a theory as to why it could?
August 4, 2008 at 1:49 pm
We moved the process from a virtual machine to a standalone machine and the problem appears to have gone away. I wonder if anyone else has seen this behavior from a VM. Any takers? 🙂
April 8, 2009 at 2:18 pm
SSIS execution can be delayed in instances where the server has no Internet connectivity.
The fix strangely enough is to open Internet Explorer (under the context of your SQL Agent account), Choose Options, and de-select the "Search for Publishers Certificate Revocation" option.
If you google SSIS, certificate revocation, you're likely to find something. Similar behavior actually also impacts SSMS opening times.
October 6, 2009 at 6:10 pm
When this problem started, did you copy a Script Task or any other component that has Visual Basic scripting inside it? I've found that if you copy a Script Task, you have to go back into "Design Script", Build (from Debug menu), and Save. This forces it to recompile the script for the component you just copied and pasted. Otherwise, it won't have a pre-compiled script, which will force compilation at execution time. Sometimes it can compile at execution; sometimes it can't. (Don't know why.) This could explain the intermittent failure, especially if you see a message in the logs like "Pre-compiled script not found; attempting to compile" (or something; can't remember the exact message).
You should go back into your package and force all of your scripts to compile by taking the steps I described above. When I did this, all my "hangs before execution" issues went away. (But then again, maybe I had an unrelated problem.)
April 23, 2015 at 8:17 am
I have a similar problem explained in more detail here:
http://dba.stackexchange.com/questions/98670/ssis-packages-get-stuck-in-sql-agent-jobs
Intermittently, SQL Agent jobs hang. There's no database activity, no logging. The DTEXEC process has 0 or a couple of seconds CPU time on it in task manager (when it has hung for days)
We can stop these hung jobs, start them again, and they hang again.
A job with a command line step then a DTEXEC step will run the command line step fine then hang on the DTEXEC step. SQL commands are also fine.
This happens about once a month. I've noticed some errors in the SQL Server log indicating the service account can't log in to it's default database, which is strange because it's SYSADM and I can log into that database. I'm not sure if its related though.
April 24, 2015 at 3:08 am
I have noticed sometimes that when I run a package interactively in BIDS (or SSDT or whatever its called now), it will hang for ages without doing anything at all, which might be the same issue as you are having when you run it via DTExec. I click the stop icon, then run it again and it usually works immediately, even though I have not changed anything in the package. As far as I can tell, it's just an intermittent bug with the stuff it does at the very beginning of an SSIS run. Maybe you could create a flag at the beginning of the SSIS job, and have a separate job that checks whether the flag is there a minute later. If the flag is not there, it kills the DTExec process that has that dtsx file name in its command line, and then starts the original job again, waits a minute and repeats if necessary.
April 26, 2015 at 7:18 am
Thanks for your suggestion but no amount of stopping and restarting jobs helps. Every single job that starts DTExec will hang and repeatedly hang until some kind of magic combination of service restarts fixes it
May 19, 2015 at 4:46 pm
So we discovered that a SQL Server restart (not agent) fixes the issue. Then it happens again. I used process explorer to check the DTExec process and there was a bunch of waiting threads, mainly mscorwks.dll!CreateApplicationContext stuck on Wait:UserRequest or
Wait:WrUserRequest or Wait:WRQueue. I found out mscorwks.dll is a .Net DLL which doesn't help much. I removed pieces of the package bit by bit until I found that it's our SSIS framework (called BIPA). When I removed the custom logger everything started working again.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply