Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 742
  • Last Modified:

SSIS, Clusters, and troubleshooting DNS

We've got a big SSIS package that imports text files into SQL Server 2005.

The thing ran fine through this massive batch of files on our Dev environment ... then on our next environment up.  

On the Test environment, the same package, working against an identical (made from the same script) DB, loading the same files, breaks with errors about the connection being forcibly closed.  The breaks appear to occur randomly.

We erroneously thought that a deployment error had caused the problem, so I closed the other question on this: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_23834194.html

Randy Knight suggested that intermittent DNS failures could be causing the problem.  As our dev / BI team talked it through, that was the most reasonable idea to come up.

So ... how do we verify that DNS failures are, or are not, happening in this scenario?
0
Daniel Wilson
Asked:
Daniel Wilson
  • 4
  • 3
3 Solutions
 
PedroCGDCommented:
Which errors?
is 32 bits or 64 bits machine?
Regards
0
 
HoggZillaCommented:
Hi,
Are you logging in the SSIS package? To a file that we can review? And, is the connection referred to in the error message the database or a file connection?
0
 
Daniel WilsonAuthor Commented:
>>Which errors?

Connection was forcibly closed by remote host.

>>is 32 bits or 64 bits machine?

64

>>is the connection referred to in the error message the database or a file connection?

database

I'll have to check on the logs ... there are some, but I may have to ask our BI lead where to find them.

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Daniel WilsonAuthor Commented:
OK, here's some more detail from our BI lead:


First of all, the failures we are experiencing in test are sporadic.  For instance, when we ran our first process run in test, we had a lot of flat files to load, but 7 failed to load, and returned errors similar to this...
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "<DB Name snipped>" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
These errors were never encountered before in dev or stage.  However, upon retrying those 7 files, with no changes made to the files or the code, 4 of them loaded, and 3 failed.  Again with similar error messages.  Upon trying those 3 remaining files, they all loaded.  This indicates to me that we are experiencing intermittent and perhaps random failures.

Also, some packages are failing occasionally, and others are failing all the time, and none of these fail in dev or stage.  Some of the error messages we have received are listed below.  However, these error messages are not consistent.
Errors in the metadata manager. Either the database with the ID of '<Cube name snipped>' does not exist in the server with the ID of '<server \  instance name snipped>', or the user does not have permissions to access the object.
The Server object is not connected.
Internal error: An unexpected exception occurred.
One thing most of these seem to have in common is some reference to communication is one way or another.
Any ideas?

We are working on getting the logs from those who have access to them ... the various walls our SarbOx auditors have set up are slowing us down ... again ...
0
 
HoggZillaCommented:
No doubt this is puzzling and frustrating. I think we have all been there. In the SSIS Package, setup a logfile. I attached a screen shot.
Next, as further debugging. Are you loading files one at a time or is there a process that grabs multiple. If multiple, you could have a pile-up of processes and a limited number of threads available. In the past I have limited concurrent processes in my SSIS packages to 2 and this has prevented that problem. In the Control Flow window click in the open space. Look at the Package properties for MaxConcurrentExecutables. Set this to 2, it is probably -1 currently.
Anyway, logging and threads are my best idea.
0
 
Daniel WilsonAuthor Commented:
One file at a time.

Still working on getting the log back from the DBA's ... and they're running a test they put together ...
0
 
HoggZillaCommented:
Oh, forgot the screenshot. This is just in case you can get a package log, not db trace or SQL Agen log. Create new connection for the file, on the details tab select everything, just in case. You may already have this, but I wanted to be clear about the log I was referring to. Access it from the menu when the package is open, SSIS, Logging..
ssis-logging.bmp
ssis-logging-details.bmp
0
 
Daniel WilsonAuthor Commented:
They think they got this working.

It involved locking the memory page for SSIS ... and making the process use the OS heap rather than its own heap.

Not being on the admin side any more, I can't say how to do any of that ...

Thanks for the ideas!
0

Featured Post

 The Evil-ution of Network Security Threats

What are the hacks that forever changed the security industry? To answer that question, we created an exciting new eBook that takes you on a trip through hacking history. It explores the top hacks from the 80s to 2010s, why they mattered, and how the security industry responded.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now