DTS Error: Importing from Excel file to SQL Server 2000

Data for Source Column 15 'Notes' is too large for the specified buffer size.

How do I get around this, I can see some of the notes entries are beyond 255 chars so I changed the destination datatype totext

I have never seen this error when importing before. What do I do?

An error I've gotten way too much. Some things you can try:

- The Excel provider (JET) often makes assumptions based on the first 8 lines of the file, and sometimes that is the problem).
- Or you can save to comma separated or tab separated file format, and import that way.

Oftentimes, I cannot figure out the problem as well. I've tried posting before, but no answer. The only thing I come up with is to save to tab-delimited, and import that way.

Sorry I can't be more help; hopefully someone has the answer.

DTS Error - Data too large for specified buffer size

I'm trying to import data into my table and I'm received an error message
that I've been able to resolve.
The message is:
Error Source: Microsoft Transformation Services (DTS) Data Pump
Error Description: Data for source column 3 ("column_name") is too large for
the specified buffer size.
The destination column size is 500 in the table and the data in the import
file for that row and column is 280 characters.
Any ideas?
Thanks in advance,
Scott
It's working now.
This link was helpful as well I switched from ODBC to OLE connection.
http://support.microsoft.com/kb/281517/EN-US/
Thanks,
Scott
"Scott Natwick" <nospamplease@.yahoo.com> wrote in message
news:j7idnVAdiJWFwz7cRVn-gA@.comcast.com...
> I'm trying to import data into my table and I'm received an error message
> that I've been able to resolve.
> The message is:
> Error Source: Microsoft Transformation Services (DTS) Data Pump
> Error Description: Data for source column 3 ("column_name") is too large
> for the specified buffer size.
> The destination column size is 500 in the table and the data in the import
> file for that row and column is 280 characters.
> Any ideas?
> Thanks in advance,
> Scott
>

DTS Error - Data too large for specified buffer size

I'm trying to import data into my table and I'm received an error message
that I've been able to resolve.
The message is:
Error Source: Microsoft Transformation Services (DTS) Data Pump
Error Description: Data for source column 3 ("column_name") is too large for
the specified buffer size.
The destination column size is 500 in the table and the data in the import
file for that row and column is 280 characters.
Any ideas?
Thanks in advance,
ScottIt's working now.
This link was helpful as well I switched from ODBC to OLE connection.
http://support.microsoft.com/kb/281517/EN-US/
Thanks,
Scott
"Scott Natwick" <nospamplease@.yahoo.com> wrote in message
news:j7idnVAdiJWFwz7cRVn-gA@.comcast.com...
> I'm trying to import data into my table and I'm received an error message
> that I've been able to resolve.
> The message is:
> Error Source: Microsoft Transformation Services (DTS) Data Pump
> Error Description: Data for source column 3 ("column_name") is too large
> for the specified buffer size.
> The destination column size is 500 in the table and the data in the import
> file for that row and column is 280 characters.
> Any ideas?
> Thanks in advance,
> Scott
>

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