Hi, I have received the following error with when executing an SSIS package with a SQL Server database as a data source where I have read only access; - Error: 0xC0202009 at my_SSIS_component, my_database: 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: "Protocol error in TDS stream". 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: "TCP Provider: An existing connection was forcibly closed by the remote host. ". 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: "TCP Provider: The semaphore timeout period has expired. ". Error: 0xC0047038 at my_SSIS_component, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "my_database" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. Service operating system; - Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (X64) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) Where I understand Windows NT 6.0 SP 2 is also known as Windows Server 2008 Service Pack 2. The error appears to occur intermittantly. Any guidance on how to arrive at a resolution would be greatly appreciated. Kind Regards, Kieran. If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood

I am connecting to my corporate network using a vpn over a dsl line. I get this error frequently when running my packages. My suspicion is that some glitch happens between the source or destination which causes this error. But I am not sure how to fix it.Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008

I've not changed anything with the package design, yet the package is running fine now. So my suspision that the root cause was a network issue is getting stronger. However I don't know what was changed to resolve it.If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood

Hi Kieran Patrick Wood, This issue might arise after the installation of windows server 2003 SP2, due to a feature calledTCP CHIMNEY. Executing the below command will disable this feature. Netsh int ip set chimney DISABLED For more details about it, please refer to: http://support.microsoft.com/kb/945977 Thanks, Eileen

Hi Eileen, Many thanks for your reply. The issue I'm facing is with a production server. Sorry I mis-reported the version number of the operating system. The server is running Windows 2008 R2 not Windows 2003 R2. Does this fix still apply. A collegeue has passed me a similar link to the one you mentioned earlier. Should we try following the instructions in this link? ; - http://support.microsoft.com/kb/951037 Would I need to perform a re-boot of the server or restart any services to apply these changes? Also I have run the SSIS package attempted to copy a large file from my desktop to the same VPN area. The SSIS package has failed with the same error however the copy still appears to be working. Thanks again, Kieran. If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood

Can anyone help me answer the above question please? I really need an answer soon.If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood

I'm continuing research into this one. Attempting to replicate the error within a domain since up to now the error is only manifest on a cross domain.If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood

Hello Kieran, I'm having a similar issue. Searching on Google I came out with a link that has a possible solution. I'm going to try with my package tonight. If I'm successful I reply here. Anyway the link is: http://bobp1339.blogspot.com/2010/09/ssis-2008-sql-server-2008-connectivity.html There seems to be something with the package connection manager. Will see. Regards, LeomarSQL Server 2008 MCITP Database Administrator SQL Server 2008 MCTS Database Developer Oracle 10g OCA

Hi Lero, Thanks for the link. I understand the core issue in my scenario is that the cross domain connection over the WAN is flaky. So we are in the process of evaluating an alternative supplier. One of the ways I identified this as the core issue is that I attempted to copy a 70Gb+ file over the cross domain and this file copy failed consistently. This means that the intermittant communications error always occurred during the period of this file copy, completely outside of SSIS. I'll study your link in more detail. Kind Regards, Kieran.If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood

Hi All,,, Anyone managed to solve the above issue? Iam facing the same problem.

Not yet. After adding another connection to some specifics data flow tasks I get less errors, but it isn't whole solved. Tomorrow I'm going to do some tests. If I have some success I'll update here. Regards.SQL Server 2008 MCITP Database Administrator SQL Server 2008 MCTS Database Developer Oracle 10g OCA

Not yet on my part. No change from my update on the 22nd December.If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood

In my environment, a SSIS package failed when it executed as a scheduled job with same errors. It seems to be caused by new TCP features on "Windows 2008 R2" Server. The ssis package didn't have the problem when it is scheduled on the servers with other OS. The issue occured only when the SSIS package was saved as a Destination Server on Windows 2008 R2. I disabled chimney only but it didn't work. Later I found additional information and I executed the following on Destination Server and resolved the issue. netsh int tcp set global autotuninglevel=disabled netsh int tcp set global rss=disabled netsh int tcp set global chimney=disabled For Your Information: Windows 2008 R2 - large file copy uses all available memory http://chicagotech.net/netforums/viewtopic.php?f=3&t=15383&sid=fc192d6ca941f0a961fa748b5df66184 Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008http://support.microsoft.com/kb/951037 found this to be a Windows 2008 R2 networking issue. New features for TCP offload conflict with broadcom nic cards. I had to turn off tcpoffload (chimney), rss, and autotuning. solution: netsh int tcp set global autotuninglevel=disabled netsh int tcp set global rss=disabled netsh int tcp set global chimney=disabled http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_26282555.html

BlueSky, I think you've put an answer for another problem I'm having with some Win 2k8 R2 instances!! LOL BuRaiR, I guess I've solved my problem. At least we have successfuly completed an execution without error. I just changed the following parameter in the connection: Use Procedure for Prepare = 2 (original = 1) OLE DB Services = Disable All (original = Default) Don't know precisely if it's all about both parameters above, but it seems to work for me. RegardsSQL Server 2008 MCITP Database Administrator SQL Server 2008 MCTS Database Developer Oracle 10g OCA

Hi all Found the solution for me!! Despite the fact that after changing connection parameters I've an overall better execution. I was still having some connections problems from time to time... As the package runs in the same server together with the databases, I've changed the connection parameter from IP to server name. This way I've LPC connections instead of TCP. I guess this maybe a server major networking issue, but for this time I have none problems reported and the server is doing very well during production hours. I don't think this is an answer for this thread, but can be an answer for somebody else. Thank you all!SQL Server 2008 MCITP Database Administrator SQL Server 2008 MCTS Database Developer Oracle 10g OCA

Hello everyone! I had a similar error when I ran my SSIS package, this error happened in a "data flow" when it tried to connect to Oracle, (I have SQL server 2005): "An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "". OnError,The PrimeOutput method on component returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing." I solved it changing the provider of my "OLE DB Source", all this inside my "data flow" in my SSIS package. I had the provider "Oracle Provider for OLE DB" and i changed it for "Microsoft OLE DB Provider for Oracle", and all works without problems. Well this works for me, maybe it can help you! Seya!

Hi, The solution was that the VPN between a Microsoft TMG firewall and a Cisco ASA (mixed vendor) under load kept collapsing the VPN tunnel. The fix was to replace Microsoft TMG with a cisco Firewall so the VPN is now cisco-to-cisco, resulting in a much more stable connection. Also a router was purchased with a higher capacity to cope with the additional traffic. I then performed an initial test where I sucessfully copied a 1/2 tb file using Windows file manager from the source server to the destination server over the new Virtual Private network connection. Where performing a file copy when the problem initially arose is where I convinced my collegues that the core issue was network related and not related to the configuration of my SSIS package. After receiving the results of the successful 1/2 tb file copy. I then executed my SSIS package over night over this new VPN connection, and the package reported successful execution within SQL Server Job Agent. Hopefully people out there will use this solution and hopefully have less pain than me resolving it. If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/kieranpatrickwood

Just to add more info to how this error and issue is presenting itself in our environment: We have a Cisco ASA 5510 in our local office, and we have an IPSEC vpn tunnel to our erp host which uses a Juniper firewall. We have both SQL 2000 and SQL 2008 R2 servers locally, and we connect and dump SQL data using SSIS packages from the remote system to our local SQL servers. The DTS package running on the local SQL 2000 server runs fine, and we never have issues. The SSIS package running on the local SQL 2008R2 server fails mysteriously. One note is that when we use Open Query syntax for some of our query commands, this seems to help the issue, although it can be very cumbersome to write using those open query statements. In our case we cannot tell our endpoint to switch routers/firewalls to be homogenous/Cisco, so we have to troubleshoot at the OS level with Windows 2008 or the SSIS package parameters being the only way to resolve it. I'll report more if we can fix it.