none
Error in SSIS while Loading data

    Question

  • Hi,

     

    I am trying to load data from one database to another database with in same sql server.Our DBA has created a new db and I am trying to load data in to this new one.But I am getting error in SSIS packages while scheduled as job. The failed job history was not showing much information So I ran the package dirently to get more info on error message.

     

    I was able to transfer around  5 to 6 tables . But I am unable to transfer the final 2 tables. I am thinking its a space issue.  Appreciate if somebidy can explain what the real problem is ?

     

     

     

    Below is the error information I got in SSIS

     

    [OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Protocol error in TDS stream". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Communication link failure". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Communication link failure". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Communication link failure". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "TCP Provider: The specified network name is no longer available. ".

     


    [DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (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.

     

    [DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.


    [DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.


    [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

    Friday, January 25, 2008 5:15 AM

Answers

  • Hi,

    This issue might arise after the installation of windows server 2003 SP2,  due to a feature calledTCP CHIMNEY.

    I have also gone through the same issue as you mentioned above. Disabling this feature will do the trick !!!!

    Executing the below command will disable this feature.

    Netsh int ip set chimney DISABLED

    Reference : http://support.microsoft.com/kb/945977

     


    Veeramani.S
    Friday, April 16, 2010 2:14 AM

All replies

  • Can somebody please help what the above error is?? Did anyone experience these errors before?

    Friday, January 25, 2008 12:26 PM
  •  

    Hi,

     

    Just to add some extra information. Our DBA has created file groups and I am using the one file grou he mentioned. Is this something related to filegroup space issue? Is this error occuring becasue the filegroup is full and could not accomodate space for those two tables.

     

    when I rtck the properties on database. I can see only 1/3 of the space is occupied. But if the database is again divided in to fiiilegrous and I am metioning to use this filegroup in create table statement.Is this something to do with file group space issue

    Friday, January 25, 2008 12:38 PM
  • Are there any error messages in the SQL Server logs?

     

    Searching for the error messages ("TCP Provider: The specified network name is no longer available." and ""TCP Provider: An existing connection was forcibly closed by the remote host.") gives a lot of hits around linked server queries. Could it be a configuration problem on the server(s)? Could they be limiting the number of connections?

     

    One thing to try on the SSIS side would be to set the RetainSameConnection property on the OLEDB connection manager to True.

    Friday, January 25, 2008 4:58 PM
  • We started receiving this error during our daily ETL.  The steps that fail involve moving data from one server to another.  It usually fails during the Execute phase of the package.  The error is unpredictable ... spontaneous.  Does not happen everytime.

     

    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: "Communication link failure".  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Communication link failure".  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "TCP Provider: An existing connection was forcibly closed by the remote host.  ". 

     

    We are struggling to resolve this.  Any help would be appreciated.

     

    Thanks.

    Thursday, October 02, 2008 6:44 PM
  • What is suggest is use Sql import export wizard to transfer the tables , You will have to define source and destination connections and the tables you want to export in your case .

    Save as a package when prompted .

    Then run the package and see if it succeeds or gives the same error message.

     

    HTH

    Thanks

     

     

    Friday, October 03, 2008 2:45 AM
  • The failure was intermittent.  Sometimes packages succeeded and sometimes not.

     

    It looks like we are beyond this issue now.  Our ETL is running successfully this morning.  One of our network guys disabled the TCP Chimey feature.  I do not know what that is so I will just provide the link he provided:

     

    http://support.microsoft.com/kb/945977

     

    Thanks for the help!

    Friday, October 03, 2008 1:44 PM
  • Hi,

    This issue might arise after the installation of windows server 2003 SP2,  due to a feature calledTCP CHIMNEY.

    I have also gone through the same issue as you mentioned above. Disabling this feature will do the trick !!!!

    Executing the below command will disable this feature.

    Netsh int ip set chimney DISABLED

    Reference : http://support.microsoft.com/kb/945977

     


    Veeramani.S
    Friday, April 16, 2010 2:14 AM
  • I faced the same issue with a package.

    It was running fine to all tables but when it reached one specific table with 7 million records, it processed till 4 million records and raised an error after that. The error was:

    Error: 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: "Protocol error in TDS stream".
    An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Communication link failure".
    An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "TCP Provider: An existing connection was forcibly closed by the remote host.

    I followed the instructions from Veera.S exectuing NETSH and it worked fine.

     

    Thank you very much for your help,

     

    Ciro


    Ciro Theodoro
    Thursday, March 24, 2011 1:47 PM
  • I have many packages running for last couple of years but lately some packages fail indefinetly. Below is the error message.

    one thing that happened different this weekend is one of the package shooted out 3500 failure emails from the event handeler

    all with the same error.

     Message

    Executed as user: NA\s021045. ...Version 10.0.1600.22 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  2:17:25 AM  Error: 2011-09-18 02:18:32.12     Code: 0xC0202009     Source: Data Flow Task OLE DB Command [289]     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: "Communication link failure".  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "TCP Provider: The specified network name is no longer available.  ".  End Error 

    My question is will the same solution mentioned above work for me?

     

    Thank you all

     

    Monday, September 19, 2011 3:18 PM
  • Our system guys have set the feature TCP CHIMNEY but still  I am getting the error.

    Litle about the setup: I run all the jobs from ETL server(2003 r2 sp2) which connects to DW server (2003 r2 sp2) to load update tables in DW Server. This is where the connection is failing. 

    ERROR:Executed as user: NA\s021045. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.1600.22 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  5:35:00 AM  Error: 2011-10-30 06:27:35.26     Code: 0xC0202009     Source: EU UPDATE [648]     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: "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.  ".  End Error 

    Is there anything more than TCP CHIMNEY. I am really tired of this error. It happens random... any job... any time.

    Any inputs, suggestions... appriciated.

    Thanks

     

     

     

    Monday, October 31, 2011 2:12 PM
  • We are getting this error on a specific job.  same as devlssis above.  Did this get solved.  It is driving us crazy.  Thanks.
    Thursday, July 12, 2012 4:05 PM