Solved

SSIS, Clusters, and troubleshooting DNS

Posted on 2008-10-22
8
725 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VM SQL server license. 1 67
Setting up two DCs 4 47
Delete duplicates from SQL Server table 2 27
SQL Server: Split string value in SELECT query only 5 30
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

752 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