September 28, 2011 at 7:02 am
I have a package that executes a stored procedure located on a different server. This procedure loads data into a table on that server. Then the package transfers the data to a repository on a different server.
The connection object to the remote server is called MonitoredServer. The connection string is held in a configuration table. Whenever I need to run this package against the remote server I edit the connection string, then run a SQL Agent job that executes the package.
The package runs fine in bids for all servers. It is successful when getting data for 22 out of 30 servers. For 8 servers, however, it fails in SQL Agent. The SQL Agent account is sa on the server it is running on AND on the computer we are gathering data from.
When the package runs against these 8 servers, it successfully executes the store procedure and loads the table on the client server. However, it will not transfer the data back to the repository.
The package is running on 32bit, Windows 2003, SQL Server 2008. The 8 servers that are failing have nothing in common - 32 and 64 bit, SQL 2005, 2008 and 2008R2.
Here is the error I get:
Message
Executed as user: {DOMAIN}\{AcctNameChangedToProtectTheInnocent}.
Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 7:47:15 AM Error: 2011-09-28 07:47:22.62
Code: 0xC0202009
Source: CollectDatabaseInfo Connection manager "MonitoredServer"
Description: 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. ". End Error
Error: 2011-09-28 07:47:22.62 Code: 0xC020801C
Source: TransferDatabaseSizes MonitoredServer [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "MonitoredServer" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error
Error: 2011-09-28 07:47:22.63 Code: 0xC0047017
Source: TransferDatabaseSizes SSIS.Pipeline
Description: component "MonitoredServer" (1) failed validation and returned error code 0xC020801C. End Error
Error: 2011-09-28 07:47:22.63 Code: 0xC004700C
Source: TransferDatabaseSizes SSIS.Pipeline
Description: One or more component failed validation. End Error
Error: 2011-09-28 07:47:22.63 Code: 0xC0024107
Source: TransferDatabaseSizes Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:47:15 AM Finished: 7:47:22 AM Elapsed: 6.875 seconds.
The package execution failed. The step failed.
Yes, I can probably redesign this, but I am just REALLY curious as to what is causing the failure.
...
September 28, 2011 at 7:20 am
Without doing a good bit more digging on the issue I'm harbouring a guess that you may have an issue with kerberos authentication on some of your servers.
You might want to run the package from the agent job again while one of your network guys watches the problem servers to see if kerberos is handing out authentication tickets on the packet requests as the SSIS server hops about your environment.
Sounds like this might be the issue anyway. I've seen this happen many times in the past when you run agent jobs calling SSIS packages which move data between different sql servers across the network.
Hope this helps.
G
September 28, 2011 at 8:47 am
What caught my eye was "Named Pipes Provider:".. What communication protocols do these 8 servers have enabled. Do the other 22 have a different setup. Also are there any aliases defined on the machine trying to reference those servers.
CEWII
September 28, 2011 at 9:51 am
Shared Memory, TCP/IP and Named Pipes are all enabled on the servers and there are no aliases.
Right now I am focusing on one server that having the issue.
It's got to be something with the login, but I can't figure out what it is. I set up the SQL Agent login in the local admin on the remote server and the server that is running the package. No luck. I then logged into the server that is running the package with the SQL Agent account and tried running the package in BIDS. Finally got the failure in BIDS (I do not get it when using my own account).
So I tried to create a connection to the remote server in the project and it succeeded. I changed the OLE DB Source without a problem. Ran the package and it fails with the DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER failure.
I can connect through SSMS with the account on both computers without a problem.
...
September 28, 2011 at 10:06 am
If you are saying that the agent account under which this package is executing has sa permissions on the remote server, then the issue is defintely not with the login.
Remember that even if you set up permissions from one server to another, that does not automatically mean that sql server can move data from one, to the other.
You may also have firewalls that are blocking access between servers.
G
September 28, 2011 at 10:12 am
You'll get a different error if the login has the wrong password/no permissions/etc. To verify this, you can check the log on the server it's trying to connect to. If there's nothing in the log about login failures, then you're looking at network issues.
Try remoting into the server you're running the SSIS package on and opening it. Check to make sure you can access the servers via the connection manager in the package (no red X's, etc). If that works, then try running the package directly (by double clicking), not via sql agent.
If you can narrow it down to where in the chain it's breaking, it will be much easier to troubleshoot.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply