December 12, 2012 at 3:33 pm
We are running SQL Server 2008 R2 on a 64bit machine, I have several SSIS package that are run via SQL Agent jobs. They will hang and never complete, it looks like they are either hanging on a file rename step or a file copy step. When I stop the job via Job Activity Monitor, the SSIS process (DTExec 32) continues to run and hangs on to some network files. Task Manager cannot close down the process neither does a taskkill command with the /f and /t parameters. We have to reboot the server to stop the processes. I obviously cannot do this on our PROD machine - we are still in DEV but our deadline is coming up and I need a solution on this. I've googled my heart out and found nothing. We have to run the packages in 32 bit mode because of some Excel exports. Should I try breaking up the package and moving the Excel steps to a separate package and keep the big processing steps in a package that can run in 64 bit? Does anyone know how to properly close down a runaway SSIS process?
Thanks,
MWise
December 13, 2012 at 12:06 am
There are lots of reasons why an SSIS package can hang. The fact that when you kill the job yet the instance of dtexec.exe persists means you have something going really wrong inside the package that the OS is allowing to continue before ending the process. What are these packages doing exactly in terms of accessing external resources? Are you doing any Office automation tasks (e.g. firing VBA code or macros from within the Excel files) that are initiated by the SSIS package?
You might start by standardizing on the 64-bit ACE drivers to talk to Excel from SSIS and then run everything under the 64-bit version of dtexec.exe. See the section Designing Integration Services Packages on 64 bit (64-bit) Computers in this article for details:
64 bit Considerations for Integration Services
From the article:
The ACE Provider and its Excel 2010 driver (64-bit, 32-bit) can be used with Integration Services under certain circumstances. For more information and to download the provider, see Microsoft Access Database Engine 2010 Redistributable.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 13, 2012 at 6:57 am
There is no Office automation going on. The packages are pretty standard: FTP task to download, File load to SQL Server in standard data flow task, File Copy and File Rename,export some summary data from the newly loaded table to Excel using Data Flow task. I've tried using the built in File System Tasks and a Script task to do the file handling - both have resulted in hangs and consequently locking files. The files aren't even that large, maxing out at 100MB. The connections to Excel are using the OLEDB/MS Jet drivers. I'm doubtful that I can convince the governance overlords to allow an install 64 bit drivers on our production server at this time as end of year lockdown starts tomorrow. I might just move out the file processing steps to a separate package and let those run in 64bit and see how that goes.
Incredibly fustrating, I've never had this problem before and of course we don't have this problem on the SQL 2K box/DTS that we are upgrading from. It's hard to champion new technology when it doesn't run as well as the old!
Thanks,
MWise
December 13, 2012 at 11:22 am
Do you have logging in place that tells you exactly which task (and command) is executing when it begins hanging?
Are you using File System Tasks or some other method to do the renames and file moves?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 9, 2013 at 2:50 pm
Yes there is logging and it clearly hangs on File System Tasks either native or written as a Script task. The most recent time was when it was hanging when copying file to DFS share drive - could be problem with DFS. However - shouldn't terminating the process on the server work? Even a taskkill /f command doesn't shutdown the process. Only a reboot of the server does. Is this because the process is running under the 32bit runtime?
Also, we were experiencing network file locks (not DFS shares) even after the SSIS process completed where I could only unlock the file if I logged into the SQL server and either rename or delete the file. Which makes no sense because my RDP session into the SQL is under my AD credentials, not the SQL job agent and the file is on a network share that I am not an admin to.
Arghhhhhh.
MWise
January 9, 2013 at 2:58 pm
DFS is another added layer that can complicate matters when manipulating files on the file system, or it could be a red herring. It could be tons of things unique or quite unique about your environment. All I could say at this point is try switching to use the 64-bit Excel driver I posted and see if running in 64-bit mode helps your situation.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 10, 2013 at 1:50 am
Apologies if you've already mentioned this - I did not see it when I skimmed the thread - but have you tried running the packages manually in BIDS? (Eek, first typo of the day - I initially wrote 'BEDS'!)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 10, 2013 at 5:36 pm
Bids runs fine or at leastwise stops when you request it to. Running in 64bit still results in the process hanging and the DTExec process not stopping when the job is stopped. At this point our Intel engineer suspects that permissions on the source file share could be set up incorrectly.
Today I ran a test package several times and found that small files copied fine and the job completed. Larger files 6mb did not but not consistently. They copied fine in BIDs. I also didn't see the problem when copying to a non DFS drive.
Mwise
May 28, 2013 at 7:09 am
I am having this problem as well. It is happening to me for flat file destinations. DTExec does not end, and cannot be killed via task manager. MUST reboot the machine. This is production server, and everything was working fine until 5/18. Suddenly random packages are hanging, and as far as I know, no updates or changes occurred to this server on that date.
The only way to kill the process is to restart the server, and this is a problem being a production server.
I really need to know whether anyone has reached a solution for this.
May 28, 2013 at 9:13 am
Get Sysinternals Process Explorer and see what dtexec has references too when it hangs. It may give some clues as to what might be going on.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 28, 2013 at 6:13 pm
Our fix was to rollback the version of Symantec and remove NTP (network threat protection). This seems to have stopped the file locking issues and unstoppable SSIS packages.
MWise
June 4, 2013 at 12:08 pm
Unfortunately, I was not able to rollback or turn off NTP. I did disable all services from starting. That did not work, though.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply