Solved

SSIS, Clusters, and troubleshooting DNS

Posted on 2008-10-22
8
721 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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
 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

786 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