September 22, 2011 at 12:37 pm
I have the following
ServerA - 2005 Standard
ServerB - Was 2000 Standard and is now 2008 Standard
TCPIP and Named Pipes are enabled on both instances.
SSIS Package - 2005, running on ServerA. This package uses Expressions & Package Configurations using parent variables to connect to multiple instances ranging from SQL 2000 to SQL 2008 R2.
Everything is 32bit
Right after ServerB was upgraded the SSIS package stopped being able to connect to this instance and started giving me the following errors:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ServerB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
component "Backup Monitor" (117) failed validation and returned error code 0xC020801C.
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [64]. ".
I can connect to ServerB manually from the package, but when I run it through my job server (it is running as a service account at that point) it starts giving me the above errors. I haven't had this problem on any other servers, including other 2008 servers.
Does anyone have any suggestions why this would have started happening? Or even other suggestions for debugging.
Thanks for any help
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 22, 2011 at 2:12 pm
Hi,
Can you post a connection string exemple?
Jeff.
September 22, 2011 at 3:30 pm
Data Source=ServerB;Initial Catalog=DBA;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
There is a package configuration that uses a parent package variable to modify the ServerName attribute.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 22, 2011 at 3:48 pm
I already have the same problems with SSIS. But with a replace of "Initial Catalog" by "Database" in the connection string it's good.
Data Source=ServerB;Database=DBA;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
Jeff.
September 26, 2011 at 9:12 am
I tried changing "Initial Catalog" to "Database" with no luck. One other thing that I have noticed is that the errors I'm getting are named pipe connection errors. I belive this is because we have named pipes configured as secondary to TCP/IP. This would mean though that the problem is probably not related to Named Pipes or TCP/IP.
Any other suggestions?
Thanks
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 26, 2011 at 1:52 pm
Some additional information: If I run a test connection on my connection manager it works. When I run the package it fails. I've tried with both Windows and SQL authentication with the same results. It also makes no difference if I remove the database/initial catalog entries entirely.
When I run the Connection manager on the server the TCP Port on IP1 and IP2 are both 1433 but IPAll is 2433.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 28, 2011 at 11:39 am
I was able to resolve the problem by changing the IPALL port to 1433 to match IP1 and IP2.
Does anyone know why this would cause an intermittent connection problem?
Thanks
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply