April 29, 2010 at 3:43 am
As part of a data warehousing project (still in development) I am running a series of SSIS packages. The package in question perform lookups on Email addresses, and also Fuzzy Lookups against name and address fields.
When my master dataset begins to get upwards of 100,000 records, the fuzzy lookup task begins to get really unstable. Sometimes a single rincoming record will crash it, and other times it won't crash until upwards of 100 records are incoming.
The errors are as follows:
[Fuzzy Lookup [30841]] 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 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Named Pipes Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [233].
I have tried resducing the set of data that the fuzzy lookup references, by creating a view with only the necessary columns and also excluding records that would not match. This partially helped, but ultimately as my dataset grows it causes the same errors.
I have searched the web high and low, and the only refernece to this error relating to a Fuzzy Lookup is my own previous topic on this forum!
I am now beginning to wonder if actually the error is totally accurate, and rather than the fuzzy lookup crashing, it is SQL Server itself.
I am not expecting a solution to this, but was wondering if anyone else has come across anything like this, or can suggest ways that I can investigate further in search of a solution.
The SSIS package runs currently on my own workstation, and the database resides on a test server.
At this stage only my workstation has the necessary version of SQL Server to run Fuzzy tasks, so I am unable to test this in another environment.
Any pointers as ever will be appreciated.
Paul
April 29, 2010 at 11:20 am
Google this phrase only:
SSIS A network-related or instance-specific error has occurred while establishing a connection to SQL Server
You'll come up with PLENTY of links.
Things you should look into off the top of my head: TempDB's space useage, running a server side Profiler trace and possibly PerfMon to determine if it's a query or hardware problem, checking for network throttling or bad connection pooling. And consider changing your SSIS package to use the same connection through out the package instead of dropping and reconnecting every time it finishes a batch / task. (Connection Manager properties -> RetainSameConnection = True)
April 30, 2010 at 2:08 am
Thanks for the reply, Brandie.
I have looked though many of the links to the google search for
SSIS A network-related or instance-specific error has occurred while establishing a connection to SQL Server
So far I have not found anything similar to my issue. They generally refer to ssis oackages, reports and .NET application not being able to connect at all, rather than the connection dropping, like my own. I will keep looking however.
Your suggestion of retaining the same database connection is a good one, and not an option that I was aware of. I am now beginning to wonder how many times during the running of my package that a connection is made! I assume that running a trace will tell me this. I am about to find out!
Yesterday I increased the size of my paging file to match the recommended (for some reason it was set to a custom (smaller!) size than the system recommended. I then left the package running overnight, processing 20,000 records at a time. It complete my entire collection of 1.2 million incoming records.
I truly hope that is the end... and in any event if this is memory related then when I deploy to a production server the issue will not arise. I am not the type to let these things lie though, and I will be doing my best to "break" it again now so that I can better understand the issue.
Many thanks for the reply!
Paul
April 30, 2010 at 4:35 am
You're quite welcome.
Given your description, though, I'm betting it's a combination of too many connections and not a big enough paging file.
May 5, 2010 at 5:44 am
Hi Brandie et al,
I have not encountered that errors again since increasing the size of my paging file. However today I tried to reconfigure my packages to use a retained connection as suggested.
However once I do this there seems to be an error occuring with the Fuzzy Grouping component. My package returns the following error:
2:27:46.81 Code: 0xC0202009 Source: UK Mailing Fuzzy
Group Fuzzy Grouping Inner Data Flow : Fuzzy Lookup [67] Description: SSIS
Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:
0x80040E37. An OLE DB record is available. Source: "Microsoft SQL Server
Native Client 10.0" Hresult: 0x80040E37 Description: "Cannot find the object
"##FuzzyLookupMatchIndex_100505_12:27:46_3004_53c1b28e-1bb7-4fa8-ae5b-daf225a0a012"
because it does not exist or you do not have permissions.". An OLE DB record is
available. Source: "Microsoft SQL Server Native Client 10.0" Hresult:
0x80040E37 Description: "Database name 'tempdb' ignored, referencing object in
tempdb.". End Error Error: 2010-05-05 12:27:46.84 Code: 0xC004701A
Source: UK Mailing Fuzzy Group Fuzzy Grouping Inner Data Flow : SSIS.Pipeline
Description: component "Fuzzy Lookup" (67) failed the pre-execute phase and
returned error code 0xC0202009. End Error Error: 2010-05-05 12:27:46.86
Code: 0xC020828A Source: UK Mailing Fuzzy Group Fuzzy Grouping [19165]
Description: A Fuzzy Grouping transformation pipeline error occurred and returned error code 0x8000FFFF: "An unexpected error occurred.". End Error
DTExec: The package execution returned DTSER_FAILURE (1). Started:
12:26:56 Finished: 12:27:47 Elapsed: 50.625 seconds. The package execution
failed. The step failed.
If I set Retain Connection to False again, all is well.
I am aware that there are indexing options for the Fuzzy Lookup item, but I didn't think there was anything you could set for Fuzzy Grouping.
Can anyone help me to get around this error please?
Paul
May 5, 2010 at 6:07 am
We can't see the error. The box is too small.
May 5, 2010 at 6:51 am
Sorry about that. I have added some line breaks to try to make it easier to read.
May 7, 2010 at 5:45 am
I have not been able to find a solution to this unfortunately.
It seems add that simply setting the RetainSameConnection to True would cause a permissions error.
May 7, 2010 at 5:50 am
Paul_Harvey (5/7/2010)
It seems add that simply setting the RetainSameConnection to True would cause a permissions error.
I've never actually seen that error before, and I've used the RetainSameConnection property. But never with Fuzzy Lookup. And that index error seems weird too.
It seems to me that the package is hiccupping and can't find the server for some reason. Not sure why.
I'm looking for information. Unfortunately, I had a whole bunch of things come up that prevented me from doing a lot of research as of yet. I hope to have alternate solutions later. But if your package actually manages to work without the RetainSameConnection being set to True, at least it's working.
May 7, 2010 at 7:18 am
Oh of course. Yes it works, and I am happy with that. I doin't want to take up too much of anybody's time.
What is quite strange is that it is a Fuzzy Grouping task that is erroring - "UK Mailing Fuzzy Group Fuzzy Grouping" but in the error it refers to a Fuzzy Lookup. This is confusing me, becuase there is no Fuzzy Lookup transformation in the package.
May 7, 2010 at 7:59 am
So far as I know, and I could be wrong, you can't Fuzzy Group without a basic Fuzzy Lookup. Which tells me SSIS has a built-in Lookup feature on the Group Task.
It wouldn't be the first time MS built code on top of code to resolve a particular problem.
December 15, 2010 at 4:29 pm
Is there a solution to 0x80040E37 error.
I tried different transaction types but still have the same error.
[Fuzzy Grouping [3893]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E37 Description: "Cannot find the object "##FLInRef_101215_18:26:23_5188_a37038f1-0a6d-4429-aa67-a2f33aaaca25" because it does not exist or you do not have permissions.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E37 Description: "Database name 'tempdb' ignored, referencing object in tempdb.".
December 16, 2010 at 4:23 am
Please start a new topic for your question.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply