ezinder
asked on
SSIS Connection Problems
Hello, Experts.
I am in much needed help resolving a problem with SSIS. I am trying to export/import data using SSIS from one SQL Cluster to another. Both are SQL 2008 Ent. 64bit. Both are running on Win2k8 Ent. 64bit. The destination is Win2k8 R2.
The problem I experience is intermittent but very frequent. The SSIS connection drops at various steps. Sometimes failing on the first task, sometimes on the last.. sometimes it completes the first iteration completely (in for-each loop) and fails on following iteration.
An identical version of this package was running against a different destination in the past without failures. The previous destination was a single server (non-clustered) running on Win2k3 Standard, and SQL 2005 Std. 64 bit.
The new destination server is completely free of traffic. Has plenty of CPU/Memory, etc.. The package has been executed from either the Source server or the Destination server with similar results.
Here is an output of a simple import/export task:
--------------------------
- Copying to [dbo].[AGGSLIVE_Bandwidth]
Messages
Error 0xc0202009: Data Flow Task 1: 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.
".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAIL
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - AGGSLIVE_Bandwidth" (49) failed with error code 0xC0209029 while processing input "Destination Input" (62). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc0209017: Data Flow Task 1: Setting the end of rowset for the buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Query" (1) returned error code 0xC0209017. 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.
(SQL Server Import and Export Wizard)
I am in much needed help resolving a problem with SSIS. I am trying to export/import data using SSIS from one SQL Cluster to another. Both are SQL 2008 Ent. 64bit. Both are running on Win2k8 Ent. 64bit. The destination is Win2k8 R2.
The problem I experience is intermittent but very frequent. The SSIS connection drops at various steps. Sometimes failing on the first task, sometimes on the last.. sometimes it completes the first iteration completely (in for-each loop) and fails on following iteration.
An identical version of this package was running against a different destination in the past without failures. The previous destination was a single server (non-clustered) running on Win2k3 Standard, and SQL 2005 Std. 64 bit.
The new destination server is completely free of traffic. Has plenty of CPU/Memory, etc.. The package has been executed from either the Source server or the Destination server with similar results.
Here is an output of a simple import/export task:
--------------------------
- Copying to [dbo].[AGGSLIVE_Bandwidth]
Messages
Error 0xc0202009: Data Flow Task 1: 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.
".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAIL
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - AGGSLIVE_Bandwidth" (49) failed with error code 0xC0209029 while processing input "Destination Input" (62). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc0209017: Data Flow Task 1: Setting the end of rowset for the buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Query" (1) returned error code 0xC0209017. 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.
(SQL Server Import and Export Wizard)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
N/A
ASKER
1. The destination database is located on a NAMED INSTANCE (i.e. 10.10.10.10\SQL_NAMED_INST
2. In sys.dm_exec_connections I see the the connections established, then closing.. so a connection is made, then drops off..
3. Lastly, (this may or may not be related) in SQL Error Log I see the following messages repeat quite frequently (not when the package runs)..
Date 6/23/2010 9:47:42 PM
Log SQL Server (Current - 6/23/2010 9:47:00 PM)
Source spid57
Message
A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 121, output error: 0).
Date 6/23/2010 9:47:42 PM
Log SQL Server (Current - 6/23/2010 9:47:00 PM)
Source spid57
Message
Error: 4014, Severity: 20, State: 13.