SSIS Package Won't Stop Running and Locking Files

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » SSIS Package Won't Stop Running and Locking...

SSIS Package Won't Stop Running and Locking Files

Rate Topic

Display Mode

Topic Options

Author

Message

MWise

Posted Wednesday, December 12, 2012 3:33 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:31 AM
Points: 381, Visits: 1,481

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

Post #1395906

opc.three

Posted Thursday, December 13, 2012 12:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 6,713, Visits: 11,748

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1396008

MWise

Posted Thursday, December 13, 2012 6:57 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:31 AM
Points: 381, Visits: 1,481

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

Post #1396170

opc.three

Posted Thursday, December 13, 2012 11:22 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 6,713, Visits: 11,748

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1396327

MWise

Posted Wednesday, January 09, 2013 2:50 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:31 AM
Points: 381, Visits: 1,481

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

Post #1405028

opc.three

Posted Wednesday, January 09, 2013 2:58 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 6,713, Visits: 11,748

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1405032

Phil Parkin

Posted Thursday, January 10, 2013 1:50 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 4,240, Visits: 9,487

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'!)

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1405267

MWise

Posted Thursday, January 10, 2013 5:36 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:31 AM
Points: 381, Visits: 1,481

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

Post #1405711

« Prev Topic | Next Topic »

Permissions