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)

Answer 1

Since I did not get enough error information in SSIS, I ran the same query in SSMS instead. That threw two errors.

Error 1

Arithmetic overflow error converting varchar to data type numeric.

I commented out more and more TSQL lines, step by step, until it ran through, so that I could trace it back to AS DECIMAL(4,2)) which threw an error since there was a 100 in the column which needs AS DECIMAL(5,2)), see also Arithmetic overflow error converting varchar to data type numeric. '10' <= 9.00.

Error 2

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

This was thrown by:

CAST(my_date1 AS DATETIME) < CAST(my_date2 AS DATETIME)

The error vanished after changing my_date1 to integer (see Convert from DateTime to INT), while the other my_date2 was already an integer anyway:

convert(int, convert(char(10),convert(date, my_date1, 104), 112)) < my_date2

---

After these two changes, everything worked with the default SSIS settings.

Answer 2

Solution for this issue is:

1. Create another connection manager for your excel or flat files else you just have to pass variable values in connection string:

2. Right Click on Connection Manager>>properties>>Expression >>Select "ConnectionString" from drop down and pass the input variable like path , filename ..

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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

Answer 9

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;