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

DT_GUID to DT_WSTR yields braces around uniqueidentifier

I'm using a Lookup component to add a DT_GUID column named cat_id to a data flow, which is used in a downstream Derived Column component to add a DT_WSTR column named value. The DC expression simply casts the uniqueidentifier to the desired type: (DT_WSTR, 434)cat_id

But when the values are persisted in the destination table's nvarchar(434) column, they have braces around them, e.g. {F475DB7F-5CB0-4EE1-9BF2-758C77D7A6D7}

What is introducing those braces, and what do I need to do to prevent it?

You may remove the braces by using SUBSTRING function in the DC expression:
http://msdn2.microsoft.com/en-us/library/ms137541.aspx
SUBSTRING((DT_WSTR, 434)cat_id, 1, 40)

(if I remember GUID length, maybe the last param should be 39 or something like this).

|||

Michael Entin - MSFT wrote:

You may remove the braces by using SUBSTRING function in the DC expression:
http://msdn2.microsoft.com/en-us/library/ms137541.aspx

SUBSTRING((DT_WSTR, 434)cat_id, 1, 40)

(if I remember GUID length, maybe the last param should be 39 or something like this).

Thanks, but why is that even necesssary? In Transact SQL, when a uniqueidentifier is cast to a varchar/nvarchar the result doesn't have braces around the value.

|||It is very common to add braces around GUID representation, although it is of course unfortunate that SSIS implemented it differently than T-SQL.|||

Michael Entin - MSFT wrote:

It is very common to add braces around GUID representation, although it is of course unfortunate that SSIS implemented it differently than T-SQL.

Thanks for following up.

BTW, GUIDs are 36 characters and strings are 1-indexed, so the expression I'm using is:

SUBSTRING((DT_WSTR,434)cat_id,2,36)