how to resolve DTS E OLEDBERROR in ssis

Question

In an  ssis package consists of  data flow task contains  OLEDB source and OLDB Target   provider is sql native client  This used to run fine   but now got an error as shown below    Please tell me how to resolve it  changing it to ado net   OS  windows 7 professional and the db is sql server 2000   Axe Data  737   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 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       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   Error  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 INDUCEDTRANSFORMFAILUREONERROR   The  input  Destination Input   62   failed because error code 0xC020907B occurred  and the error row disposition on  input  Destination Input   62   specifies failure on error  An error occurred on the specified object of the specified component   There may be error messages posted before this with more information about the failure    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

User · Answer

I would start by turning off TCP offloading. There have been a few things that cause intermittent connectivity issues and this is the one that is usually the culprit.

Note: I have seen this setting cause problems on Win Server 2003 and Win Server 2008

http://blogs.msdn.com/b/mssqlisv/archive/2008/05/27/sql-server-intermittent-connectivity-issue.aspx

http://technet.microsoft.com/en-us/library/gg162682(v=ws.10).aspx

http://blogs.msdn.com/b/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx

User · Answer

If it is related to the SSIS Package check may be possible that your source db contains few null rows  After removing them this issue will not appear any more

User · Answer

I faced the similar issue   Deselect the check box   In wizard deselect the checkbox stating  First row has columns names   and before running the wizard make sure you have opened your  excel sheet   Then run the wizard by deselecting the checkbox   This resolved my issue

User · Answer

Solution for this issue is    Create another connection manager for your excel or flat files else you just have to pass variable values in connection string  Right Click on Connection Manager  properties  Expression   Select  ConnectionString  from drop down and pass the input variable like path   filename

User · Answer

Knowing the version of Windows and SQL Server might be helpful in some cases   From the Native Client 10 0 I infer either SQL Server 2008 or SQL Server 2008 R2   There are a few possible things to check  but I would check to see if  priority boost  was configured on the SQL Server   This is a deprecated setting and will eventually be removed  The problem is that it can rob the operating system of needed resources   See the notes at   http   msdn microsoft com en-in library ms180943 v SQL 105  aspx  If  priority boost  has been configured to 1  then get it configured back to 0   exec sp configure  priority boost   0  RECONFIGURE

User · Answer

I ve just had this issue and my problem was that I had null rows in a csv file  that contained the text  null  rather than being an empty

User · Answer

I had a similar issue with my OLE DB Command and I resolved it by setting the ValidateExternalMetadata property within the component to False

User · Answer

I had this same problem and it seemed to be related to using the same database connection for concurrent tasks  There might be some alternative solutions  maybe better   but I solved it by setting MaxConcurrentExecutables to 1

[sql-server] how to resolve DTS_E_OLEDBERROR. in ssis

Examples related to sql-server

Examples related to ado.net

Examples related to oledb

Examples related to ssis