DTS Buffer times out when inserting to sql destination

I have a lookup component which determines if a record is to be updated or inserted. If it does not find match for a particular row that row is sent to the error output of the lookup component from where it is bulk inserted into the database using sql server destination.

Now the problem is when there are no rows to be inserted, the DTS buffer times out throwing an error. However if i increase the timeout or set it to 0, it hangs on indefinitely.

Is there a way that i can ignore the sql server destination when there are no rows to be inserted.

Thanks

[SQL Server Destination [590]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

What happens when you replace the SQL Server destination with an OLE DB destination?|||

Amna wrote:

I have a lookup component which determines if a record is to be updated or inserted. If it does not find match for a particular row that row is sent to the error output of the lookup component from where it is bulk inserted into the database using sql server destination.

Now the problem is when there are no rows to be inserted, the DTS buffer times out throwing an error. However if i increase the timeout or set it to 0, it hangs on indefinitely.

Is there a way that i can ignore the sql server destination when there are no rows to be inserted.

Thanks

[SQL Server Destination [590]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

How big is the data set being processed?

It is likekly the SQL Server destination is trying to insert the whole data set in one transaction. If you use OLE DB DEtination you will have more control over the commit and batch size.

|||The problem is when there are NO records. When there are records to be inserted, SQL server destination works fine. But when there are no records, it kinds of start waiting for the records and then times out. Any ideas?|||

Hi Amna,

feel free to open a bug for this on the connect site.

In the meantime, you should use the OLE DB destination with Fast Load. It should not have a significant impact on the performance.

Thanks,

-Bob

DTS Buffer times out when inserting to sql destination

I have a lookup component which determines if a record is to be updated or inserted. If it does not find match for a particular row that row is sent to the error output of the lookup component from where it is bulk inserted into the database using sql server destination.

Now the problem is when there are no rows to be inserted, the DTS buffer times out throwing an error. However if i increase the timeout or set it to 0, it hangs on indefinitely.

Is there a way that i can ignore the sql server destination when there are no rows to be inserted.

Thanks

[SQL Server Destination [590]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

What happens when you replace the SQL Server destination with an OLE DB destination?|||

Amna wrote:

I have a lookup component which determines if a record is to be updated or inserted. If it does not find match for a particular row that row is sent to the error output of the lookup component from where it is bulk inserted into the database using sql server destination.

Now the problem is when there are no rows to be inserted, the DTS buffer times out throwing an error. However if i increase the timeout or set it to 0, it hangs on indefinitely.

Is there a way that i can ignore the sql server destination when there are no rows to be inserted.

Thanks

[SQL Server Destination [590]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

How big is the data set being processed?

It is likekly the SQL Server destination is trying to insert the whole data set in one transaction. If you use OLE DB DEtination you will have more control over the commit and batch size.

|||The problem is when there are NO records. When there are records to be inserted, SQL server destination works fine. But when there are no records, it kinds of start waiting for the records and then times out. Any ideas?|||

Hi Amna,

feel free to open a bug for this on the connect site.

In the meantime, you should use the OLE DB destination with Fast Load. It should not have a significant impact on the performance.

Thanks,

-Bob

DTS and Co Question?

How can i copy data from one db1 table a to db2 table b?
1) all new data should be inserted
2) all other data should be updated
(ID's should be the same in both db-tables)
thx, mfg Daniel
How do you want to update the data?
If want to update just by TSQL then First run Execute SQL Task to update
the data and then as next step run transform data task it will append
the data.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***

DTS and Co Question?

How can i copy data from one db1 table a to db2 table b?
1) all new data should be inserted
2) all other data should be updated
(ID's should be the same in both db-tables)
thx, mfg DanielHow do you want to update the data?
If want to update just by TSQL then First run Execute SQL Task to update
the data and then as next step run transform data task it will append
the data.
Regards
Amish
*** Sent via Developersdex http://www.developersdex.com ***

DTS and Co Question?

How can i copy data from one db1 table a to DB2 table b?
1) all new data should be inserted
2) all other data should be updated
(ID's should be the same in both db-tables)
thx, mfg DanielHow do you want to update the data?
If want to update just by TSQL then First run Execute SQL Task to update
the data and then as next step run transform data task it will append
the data.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***