January 25, 2014 at 11:38 am
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.
January 26, 2014 at 2:25 pm
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/[/url]
__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
January 26, 2014 at 11:17 pm
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
January 27, 2014 at 12:35 am
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
January 27, 2014 at 3:40 pm
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 ??
January 27, 2014 at 5:15 pm
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.
January 30, 2014 at 9:12 pm
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.
January 31, 2014 at 5:36 am
Place I used to work at just had similar issue when they moved to new server.
Sounded like network traffic was the issue.
January 31, 2014 at 4:42 pm
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.
January 3, 2018 at 11:55 am
Hi, we are encountering the same issue currently. Error message is exactly the same and the error happens sporadically. I am wondering what was next after the network trace. Any update is highly appreciated.
March 15, 2018 at 11:20 am
Has this issue been resolved? I am experiencing the same issue. Did it turn out to be a network issue?
July 31, 2020 at 5:27 pm
Have PowerShell script that repeatedly invokes SQLCMD. Of the hundreds of invocations, a random handful will fail with this error on every invocation of the script. The script is run on the host system connecting to a SQL 2016 SP2 instance running on Windows 2016 as a VM (VMWare Workstation). So network issues would not seem to be a factor (all on same physical "box"). Same script run against SQL 2016 SP2 instance on host OS (Windows 10) never has issues.
The VM and host system are not overloaded, no contention for memory, CPU, or disk (SSD). VM configured with 8 processors (2 with 4 cores each), 32 GB RAM. Host has 128 GB RAM with 48 logical processors (2 sockets, 24 cores).
July 31, 2020 at 5:56 pm
I would eliminate network just yet. Script is running under some account, which might not be a local account.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply