Execute SQLTask Error String or binary data would be truncated.

Question

Hi There.

I've got an SSIS Package that I'm trying to get to insert a text field into an Excel spreadsheet text field that runs larger than 255 characters.

I'm getting this error:

----------------------------------------------------------------------------------

Error: 2011-08-09 08:45:48.76

   Code: 0x00000000

   Source: US Decorated Catalog Execute SQL Task 

   Description: String or binary data would be truncated.

End Error

----------------------------------------------------------------------------------

I've changed the actual field in the Excel spreadsheet to a text field by 

1. Opening the file

2. Right clicking on the field->Format Cells->Text

however that doesn't seem to work.

Do I need to modify the INSERT statement?

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\server\Spreadsheets\Catalog.xls;',
'SELECT 
ItemNumber,
ItemName,
ItemDescription
FROM [Excel_Destination$]')
select  
CAST(pNum AS varchar(15))	AS ItemNumber, 
CAST(pName AS varchar(50))	AS ProductName, 
CAST(pd AS ntext)			AS ItemDescription
FROM
(
SELECT Distinct 
CAST(Product.ProductNumber AS varchar(15)) AS pNum, 
CAST(ProductName.Value AS varchar(50)) AS pName, 
CAST(ProductDescription.Value AS varchar(MAX)) AS pd
WHERE   
...
)
WHERE   
...
)t
ORDER BY ItemNumber

This has been tying me up in knots for a couple of days now..

 

thanks in advance for any help.

 

doug

 

Answer 1

This is the answer:

As per Todd McDermid:

If you want to send more than 255 characters to Excel, you need to convert the SSIS column to a DT_NTEXT data type.  Excel will only accept DT_WSTRs of 255 characters or less.

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ce87f05e-81f5-4ccf-887c-31583f47d212

---

Arthur My Blog

Answer 2

So I guess I'm a little confused on where to convert the SSIS Column to DT_NTEXT.

Answer 3

I often use the Derived Column Transformation to do that.

---

Arthur My Blog

Answer 4

I see.

 

well, the problem is, that this code was already implemented using the INSERT INTO OPENROWSET code I posted above and I just need to fix that particular field that's being truncated.

I saw a post where it looked like they were actually dropping the excel tab like a table and creating a new tab with typed columns.

http://www.sqlservercentral.com/Forums/Topic349843-148-1.aspx

I think it's the second post.

 

Would that work?

Answer 5

I would not use Openrowset, but rather Excel destination

---

Arthur My Blog

Answer 6

Is it possible to modify what I have to get what I need?

The reason being is that there are 10 of these things all interconnected in an SSIS package flow.

So i kind of need to keep the code the way it is.

Thanks.

Answer 7

anyone?

Answer 8

I see your point now, if too much work try the solution at the bottom of http://www.sqlservercentral.com/Forums/Topic1079543-338-1.aspx#bm1081856

---

Arthur My Blog

Answer 9

Yeah,

 

I think this job that I was working on was a little bit of a hack.  There were 8 file transformations using OPENROWSET, so I just pulled all of of the SQL out and created 8 new dtsx files using an excel destination object.

Once i did that, it was all downhill.

 

thanks.

 

doug