Solved

SSIS, Clusters, and troubleshooting DNS

Posted on 2008-10-22
8
713 Views
Last Modified: 2013-11-10
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
Comment
Question by:Daniel Wilson
  • 4
  • 3
8 Comments
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 200 total points
ID: 22781275
Which errors?
is 32 bits or 64 bits machine?
Regards
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22781467
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
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 22784970
>>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
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 22785478
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 17

Assisted Solution

by:HoggZilla
HoggZilla earned 300 total points
ID: 22785874
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
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 22786002
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22786108
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
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 0 total points
ID: 22895277
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I've written instructions for one router type, but this principle may be useful for others of the same brand and even other brands of router. Problem: I had an issue especially with mobile devices that refused to use DNS information supplied via…
Resolve DNS query failed errors for Exchange
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now