Failed to acquired connection
Hi All, I have created a package for extracting data from a table and split the value of one column into three different rows. I use execute sql task to extract all data and load into a variable. Then use a For each loop. There is another execute sql task inside the loop. I am using OLEDB data connection. The package runs for certain number of records and then throws couples of error messages. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired". 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: "Named Pipes Provider: Could not open a connection to SQL Server [5]. ". End Error Error: 2011-03-11 09:38:26.74 Code: 0xC00291EC Source: Insert RecordCount History Execute SQL Task Description: Failed to acquire connection "MyDB". Connection may not be configured correctly or you may not have the right permissions on this connection. Also when I disable the for each loop part , package runs without giving an error. My local machine operating system is windows xp 32 bit while dtexec utility installed in my local machine is 64 bit. SQL server is 2008 R2. SSIS is 2008. I changed Run64bit to False under debugging option in BIDS. But nothing has changed. Also i tried chaging delay validation of the execute sql task to TRUE. Still getting the same errors. 1. Is this an SSIS issue or network related issue or something else? 2. As this works for certain number of records, this should not be a firewall issue ..right? 3. Could this be a permission issue? again as this runs for certain number of records, it should not an issue? 4. Or may be resource issue? 5. Can you please tell me how to fix this error ? Thanks shamen
November 8th, 2011 11:53pm

If the operating system wherein the SQL is installed is using Windows Server 2008 R2 it could be the firewall that is blocking the connection. See: http://msdn.microsoft.com/en-us/library/cc646023.aspxRandy Aldrich Paulo MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog
Free Windows Admin Tool Kit Click here and download it now
November 9th, 2011 1:16am

HI Randy, Thanks for your reply..If so, why does the package runs for first couples of records but not for all? Thanksshamen
November 9th, 2011 1:27am

Probably the outgoing connection is allowed but when the response from SQL (Source/Dest) comes in specially if it uses Dynamic Ports then the firewall blocks it. The default setting of the firewall is to Allow Outgoing Connection but block Incoming Connections which doesnt match the rule. Try disabling firewall (incoming) see if it works. BTW, you can check the Event Log, there should be a log if the packet is being dropped. Randy Aldrich Paulo MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog
Free Windows Admin Tool Kit Click here and download it now
November 9th, 2011 4:07am

ok Thanks I will check that today. shamen
November 9th, 2011 9:57am

When I changed the retained the same connection property of the OLEDB connection to TRUE, it works. I am not sure why.shamen
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2011 1:30am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics