SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL JOB Failing Unable to complete login process due to delay in prelogin response


SQL JOB Failing Unable to complete login process due to delay in prelogin response

Author
Message
dba.sql29
dba.sql29
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1281 Visits: 1275
Hi Experts,

One of my SSIS job is failing due to error below. I have one server "A" where all SSIS package are sitting and the job is making connection to different prod server , server B. This Job runs every hour and failing randomly. It runs fine for 3 to 4 hours and then fails. I have added the SQL AD account and made him SA on server B but still its failing. Did any one resolve this issue?

Thanks for your help.

Error:
Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 8:00:03 AM Error: 2014-01-25 08:00:18.85 Code: 0xC0202009 Source: DimAppealDecisionReason Connection manager "Facets OLE DB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Unable to complete login process due to delay in prelogin response". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "TCP Provider: Timeout error [258]. ". End Error Error: 2014-01-25 08:00:18.85 Code: 0xC020801C Source: DFT Source to SourceStage OLE_SRC Appeal Decision Reason Extract [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager Database OLE DB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2014-01-25 08:00:18.85 Code: 0xC0047017 Source: DFT Source to SourceStage SSIS.Pipeline Description: component "OLE_SRC Appeal Decision Reason Extract" (1) failed validation and returned error code 0xC020801C. End Error Error: 2014-01-25 08:00:18.85 Code: 0xC004700C Source: DFT Source to SourceStage SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-01-25 08:00:18.85 Code: 0xC0024107 Source: DFT Source to SourceStage Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:00:03 AM Finished: 8:00:18 AM Elapsed: 15.366 seconds. The package execution failed. The step failed.
peterjonk
peterjonk
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 679
From where is the package called?
If the SSIS package is called from a SQL Agent job you will need a proxy account to access the SSIS subsystem.

The article below explains what to do.
http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
dba.sql29
dba.sql29
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1281 Visits: 1275
Thanks for reply but in error message it don't indicate its a proxy issue, it says below error.

OLE DB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "[highlight=#ffff11]Login timeout expired[/highlight]". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "[highlight=#ffff11]Unable to complete login process due to delay in prelogin response[/highlight]". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "[highlight=#ffff11]TCP Provider: Timeout error [258[/highlight]]. ". End Error Error: 2014-01-26 20:45:18.71 Code: 0xC020801C
peterjonk
peterjonk
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 679
It could also be a firewall issue. Check the list below.
http://technet.microsoft.com/en-us/library/ms190181%28v=sql.105%29.aspx

Also check the native client version. Depending on the SSIS version you might need version 10.1 instead of 10.0.

__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
Greg Edwards-268690
Greg Edwards-268690
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13196 Visits: 8815
You might want to look at this and try increasing the values.
dba.sql29
dba.sql29
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1281 Visits: 1275
Thanks Guys ,

I have increased time out session from 600 to 2000 and SSIS package is running on 10.1 version. I haven't found the Root Cause of this issue. Some one did say restarting Server might as they did every time this happened on there server but I don't see that as a solution.

Any Ideas ??
Greg Edwards-268690
Greg Edwards-268690
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13196 Visits: 8815
Sporadic issues can be very difficult to pin down.
And depending on physical and virtual machines in the mix, troubleshooting can be challenging.

We used SCOM, so I had the luxury of being able to get some pretty good reports from a number of machines after the fact.
Lacking that, you might want to try capturing some of the basics on the 2 machines for a few minutes around the run time.

You also might want to mention this to whoever is Domain Admin.
More as a 'I'm having this problem...do you have any ideas on how to troubleshoot it?'
Most domain admins are glad to be able to prove authentication is working fine.
And may have some ideas on what they think you should check.
dba.sql29
dba.sql29
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1281 Visits: 1275
Thanks for reply ,

We are going to set up a network trace tonight. Other thing I was thinking we have Transaction replication that is on 24 hours and SQL might be fighting for resources.
so tomorrow we are going to turn off replication and see if that helps. Microsoft don't have a clue what could be root cause of this problem.
Greg Edwards-268690
Greg Edwards-268690
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13196 Visits: 8815
Place I used to work at just had similar issue when they moved to new server.
Sounded like network traffic was the issue.
dba.sql29
dba.sql29
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1281 Visits: 1275
Funny .... We are running network trace from yesterday and problem has not occurred.
Wonder WHY??? But right now its point to wards Networks.
will keep you posted.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search