error in both ssis and ssms - something about losing connections - new error
-
hi for about 4 or 5 days now, i've been seeing various connections (to our dw server) issues in ssis (excel to sql) under vs 2022 AND SSMS. the ssis error is shown below. in ssms it looks like this ... The connection is broken and recovery is not possible. The connection is marked by the server as unrecoverable. No attempt was made to restore the connection.
does anybody recognize this? its as if a setting on our sql server is now only allowing so much i/o before it drops the connection. in ssis about ???? (it wasnt all 50,000 , more like 13k and that may have been on 2 runs) records get loaded from excel to sql before i lose connectivity.
[OLE DB Destination [794]] Error: 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 11.0" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "TCP Provider: The network connection was aborted by the local system.
".
[OLE DB Destination [794]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (794) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (807). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[Excel Source [16]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Excel Source returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
-
i thought the pkg was dying on record 1000 but that isnt true. i'll recalibrate. at the moment it looks like i have about 12000 + records perhaps from one or 2 runs of the package. i got this spreadsheet from someone else and am just noticing how huge it is horizontally (ie #columns). i'm going to copy just one column into another tab and see how far i get.
-
i tried loading just one column, the one with product in it. and a constant string in the other column created in a derived column transform component. removing most of the noise in excel. hoping to see the pkg load another ? records at least. but i got this weird message after closing and reopening vs. Before closing vs i tried a run and got a communication error before even one record loaded on this one column version.
[SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.
then i tried running again (not as admin) and got this familiar set of errors...
[OLE DB Destination [31]] Error: 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 11.0" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "TCP Provider: The network connection was aborted by the local system.
".
[OLE DB Destination [31]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (31) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (44). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[Excel Source [15]] Error: Setting the end of rowset for the buffer failed with error code 0xC0047020.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Excel Source returned error code 0xC0209017. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
-
You can ignore that 'global shared memory' warning. It does not affect how your package runs.
Have you tried running the import on a dev machine (with both SQL Server and Excel installed locally), to work out whether this is a network issue and/or an SSIS/Excel issue?
You could also try exporting from Excel to CSV and importing the CSV. SSIS generally works better with CSV (or TSV, or pipe-delimited) files.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
thx Phil, at the moment its all on my local pc. i've been thinking about running it right on the sql server 1. under vs and 2) from the catalog. I'd get the spreadsheet over there. Not sure how the catalog approach could see a file path but never tried that before.
here is an update.
-i ran ssis multiple times today from vs 2022 and all 50k records loaded into sql without an error. Unfortunately it took at least 30 minutes each time to do what normally takes a minute or 2. i wouldnt know if anything anywhere was changed that at least stopped this from aborting.
-notice below where the debugging package sits for most of the 30 minutes.
-notice below what task manager shows while the pkg is debugging.
-notice below the version of excel i have installed. I have changed the version of excel in the pkg to 2016 from i think 2007-2010 with no improvement.
-i cant stop thinking ssms also acted somewhat funny in the last few days. i dont normally see that familiar transport error more that once or twice a year and i've seen it 3-4 times over the last few days.
-
i should have mentioned that the multiple runs today were over vpn from my home. whereas yesterday all the failing was inside the firewall at work, ie on prem internally. so i suppose vpn'd i might be experiencing a similar issue as inside the firewall but may be getting a break. i dont know how this pkg would have behaved had i been in my office today.
-anyway i ran the same pkg as admin. still took 30 minutes. there were 2 reasons i ran as admin, 1) i saw that suggestion in one of the errors, 2) we had an incident here a month or two ago after which admin accts seemed to become more important and regular ids were pretty much forbidden on most of our servers.
-i'm going to try csv, then sql to sql. depending on what i find , i might have to open a ticket with our dba or network group or MS.
-
-update...i ran the pkg with a csv file as an ssis flat file connector. and it took just as long, approx 30 minutes. all i can conclude from this is that excel is not at fault. i am going to try sql to sql next.
-
i ran the pkg sql to sql. it ran in less than 5 seconds. because i have a derived column component between the sql frokm and to adapters, i believe that precludes a network issue, unless ssis is smart enough to delegate that derived column transform to the engine. otherwise, i believe the derived column makes the path of data flowing thru the buffer come back to my pc. thoughts?
-
i ran the pkg from csv to sql on the sql server itself in 25 SECONDS. Not sure what to conclude from this but i'm tempted to open a ticket with MS.
for some reason the 1st pkg i was developing on the sql server couldnt see my data tab in an excel file even after i converted that tab to "table". not going to worry about that right now. while it doesnt seem like it should matter, excel isnt installed on that server.
- stan wrote:
i ran the pkg sql to sql. it ran in less than 5 seconds. because i have a derived column component between the sql frokm and to adapters, i believe that precludes a network issue, unless ssis is smart enough to delegate that derived column transform to the engine. otherwise, i believe the derived column makes the path of data flowing thru the buffer come back to my pc. thoughts?
When you run a package in VS on your local PC, it spawns a DTExec process on your PC to perform the execution, so all of the data in a dataflow would travel from source --> your PC --> target.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
thx Phil. I sent the issue to our ops/network guys.
I provided them with examples of 3 products i use that i believe use the "transport layer" to do their thing and have gone "bananas"...
- ssis (60 x the normal run time when I'm vpn'd in from home and running an excel to sql etl under vs, or same simple pkg ALWAYS fails with a connection issue when run from behind the fire wall, ie at work)
- ssms, more occurrences of that familiar red transport/unrecoverable error in one day than i usually see in a year
- excel pivots over ssas, i've never seen a transport error here till now
-
i think we have a partial answer. I dont know for how long but apparently when in the office i was on wifi. One of our ops guys who looked at my ticket pinged me last week while i was at the office asking if i was on wifi. i looked and was surprised to see that i was. i switched to ethernet and nothing was working. i let a network guy know and he came over quickly to repair my cabling. we noticed other folks were getting transport errors while on wifi and have made folks generally aware of what that probably means.
my understanding is that at the moment our office wifi isnt playing well with certain kinds of communication.
to convince myself the cable repair did the trick i ran the original pkg under ethernet at the office today and it ran without losing the connection and without aborting.
i dont think i am out of the woods yet. the pkg only loads 22 k records but took 8 minutes to run on the lan. if i am not mistaken, similar pkgs never took longer than half a minute to run on prem on my pc. and this pkg in particular runs on our sql server in about half a minute. i still have to face the 60x factor when vpn'd. i may switch gears at home and just use a cable.
i feel like the remaining factors (if there really are some) could possibly come down to memory constraints on my pc messing up the ssis buffer architecture, network issues even on lan, visual studio/ssis issues when run on my pc , maybe the fact that my source spreadsheet is in the cloud and some sort of obvious explanation that i am overlooking. i did look for spikes before when the pkg was aborting on my pc on prem under task manager , under vs and dont recall any memory spikes.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply