none
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

     

    Tuesday, August 9, 2011 1:02 PM

Answers

  • 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

    • Marked as answer by drdexter33 Thursday, August 11, 2011 1:31 PM
    Thursday, August 11, 2011 1:31 PM

All replies