February 13, 2008 at 12:51 pm
We are having some issues with our ETL processes using SSIS. We occansionally get the following error with one or more of our packages:
Could not load package "DWODS-LoadZANGLEattphlog" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Unable to complete login process due to delay in opening server connection). The SQL statement that was issued has failed. Source: Started: 8:44:17 PM Finished: 8:44:56 PM Elapsed: 38.781 seconds. The package could not be loaded. The step failed.
The fix at this time, restart the job at the step that failed. We can't have someone sitting around watching the process as it runs nightly. I have run traces and Profiler but have not been able to catch anything that would help me identify the problem.
Has anyone else had a problem like this, and if so have you been able to identify what the problem is.
Thank you.
😎
February 20, 2008 at 10:28 am
We are still having a problem with this. If anyone has any ideas they would be greatly appreciated.
😎
February 21, 2008 at 8:55 am
well i haven't seen that error before. but since you are desperate for a solution why don't you create a new separate job and put in it only the step(that fails) that you need to restart. Schedule it so it executes around a minute after the original job.
If this worked for you at least you don't have to monitor it in the middle of the night until u find the real problem.
As for the problem, i'm guessing here, maybe the step before the troubled step is holding a SQL connection that the troubled step is using. The DB connections in SSIS can be used in different packages so maybe you need to create a new separate connection for this troubled step.
February 21, 2008 at 9:15 am
Well it seems to be connection / sql statement timeout error. I never had this error but I guess from the error description that connection is timing out.
I dont think that you can capture timeout event using profiler but It is possible that server is quite busy (check the backup job) at the time when connection or sql statement execute statement passed to server and could not respond in timely fashion. Please note as this behaviour is not consistent it also suggest that it has to do some external factor on the server.
Try to increase timeout as it might help or to change the time of runing the job so it run less busy time.
hope this helps.
Regards,
Furrukh baig
February 21, 2008 at 9:17 am
I wish it were that simple. All of the jobs have multiple steps, one even has 60+ (I'd have to open it to get the exact number and I'm being lazy). It is a random/intermittent error that can occur on step 1 or step 25 or .... We added retries to the jobs and that has helped a bit, but still have the problem when 1 or 2 retries weren't enough. We could keep increasing the number of retries, but that is just a band aid fix.
I am working on an alternative process that I hope is more robust, it would just be nice to figure out what is causing the problem we are currently experiencing.
February 21, 2008 at 9:37 am
Also, can't change the time when the jobs run, part of the process has to be completed prior to 9:00 PM local time to ensure data is transferred to a Managed Service Provider. We will look at tchanging the timeout period, and yes the server could get busy while these jobs are running as it is the initial phase of our Data Warehouse ETL process. The backups aren't part of the issue, as these are completed as the last steps in the ETL process, so there is no conflict there.
😎
March 6, 2008 at 3:53 pm
Getting a similiar type of intermittent error with a SSIS maintenance plan job that backs up transaction logs. This is with SQL 2005 SP2:
Executed as user: DMZ\!svc.sql. 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: 9:36:19 PM Could not load package "Maintenance Plans\User Dbs" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Unable to complete login process due to delay in opening server connection). The SQL statement that was issued has failed. Source: Started: 9:36:19 PM Finished: 9:37:09 PM Elapsed: 50.438 seconds. The package could not be loaded. The step failed.
Any ideas or help would be greatly appreciated!
March 6, 2008 at 4:16 pm
Actually, somewhere on this site I found a similiar (yet different) problem. The solution recommended was adding this:
127.0.0.1crl.microsoft.com
to the hosts file on the server.
I asked our network services people if they could do that, and since it was added, I haven't had any problems with login timeouts (yet). I am still having intermittent issues with some of the packages just hanging.
Hope this helps you with your issue.
😎
March 7, 2008 at 8:40 am
Thanks Lynn! I'll give that a try and see how it works.
March 7, 2008 at 12:48 pm
I was getting a SQLHandShake failure from time to time on some backup jobs going to a remote server. Added the entry to the HOSTS file and haven't seen the issue since. Although, now that Im bragging about it, it'll probably occur tonight.:unsure: Sounds remarkably similar!
-- You can't be late until you show up.
July 23, 2008 at 1:41 pm
I'm getting the same error when trying to execute a package from an Operating system (CmdExec) step within a SQL job. The command line is:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /SQL "\Reports\S3MissingFilesReport" /SERVER NRTETLSCL001 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
FYI, the job does nothing but execute the job from the OS step, then send a success or failure notification email. (We're calling the job via OS step rather than an SSIS Package step in order to run the package in 32-bit mode. Some components within the package won't run in 64-bit mode.)
I'm getting the same sporadic/random error as the original poster:
Executed as user: HQDOMAIN\!svcSQLAdmin. 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: 11:15:00 AM Could not load package "\Reports\S3MissingFilesReport" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Unable to complete login process due to delay in opening server connection). The SQL statement that was issued has failed. Source: Started: 11:15:00 AM Finished: 11:15:40 AM Elapsed: 40.109 seconds. Process Exit Code 5. The step failed.
I added the suggested entry in the hosts file, but am still getting the error. I've just tried removing the /SERVER switch from the command line at the suggestion of a post regarding a similar problem. I'm hoping this will have some impact, but if anyone has any other suggestions, I'd love to hear them.
Thanks all!!
October 6, 2009 at 2:42 pm
BUMP
Did anybody ever get a satisfactory error? Could this problem be related to the the TEMP/TMP locations being filled?
Thanks
October 6, 2009 at 2:56 pm
Not I...problem just sorta went away
October 6, 2009 at 3:19 pm
Dang.
I am starting to suspect this may be related to memory being a limiting resource on this server ( only 2 GB and the DB was not constrained...only around 150 MB free on the server and while watching I did see free go down to 35 MB at one point ). I'm adjusting and will see if the problem goes away.
October 6, 2009 at 3:37 pm
Lynn,
You don't happen to have the Priority Boost set to ON do you? I have seen that when a server is busy enough and especially when that is set that it won't respond to login requests. I also agree that your login attempt timeout should wait longer, say 30-60 seconds because I believe the default is 15 and on a server that is busy that may be too short.
CEWII
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply