SSIS Truncation may occur due to inserting data from data flow column

0

I built Foreach Loop Container in SSIS that extracts data from multiple excel (*.xlsm) files in a directory, and then writes the results in another location.

[Final BCS Description] is causing me problems. I tried using data conversion DT_WSTR.

enter image description here

I am getting error:

Truncation may occur due to inserting data from data flow column "Copy of Final BSC Description" with a length of 2000 to database column "Final BSC Description" with a length of 255.

This doesn't make sense. "Final BSC Description" in the sources is greater than 255 and there is no cap in destination. Could you someone provide a solution or a work around.

Error message below.

SSIS package  starting.
Information: 0x4004300A at Load Excel Files, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at Load Excel Files, Excel Destination [12]: Truncation may occur due to inserting data from data flow column "Copy of Final BSC Description" with a length of 2000 to database column "Final BSC Description" with a length of 255.
Information: 0x4004300A at Load Excel Files, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at Load Excel Files, Excel Destination [12]: Truncation may occur due to inserting data from data flow column "Copy of Final BSC Description" with a length of 2000 to database column "Final BSC Description" with a length of 255.
Warning: 0x80049304 at Load Excel Files, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.
Information: 0x40043006 at Load Excel Files, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Load Excel Files, SSIS.Pipeline: Pre-Execute phase is beginning.
Error: 0xC0202009 at Load Excel Files, Excel Destination [12]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "Microsoft Access Database Engine"  Hresult: 0x80040E21  Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC002F445 at Load Excel Files, Excel Destination [12]: An error occurred while setting up a binding for the "Control Design Rationale" column. The binding status was "DT_NTEXT".
Error: 0xC0202025 at Load Excel Files, Excel Destination [12]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Load Excel Files, SSIS.Pipeline: Excel Destination failed the pre-execute phase and returned error code 0xC0202025.
Information: 0x4004300B at Load Excel Files, SSIS.Pipeline: "Excel Destination" wrote 0 rows.
Information: 0x40043009 at Load Excel Files, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Load Excel Files
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package  finished: Failure.
excel
ssis
ssis-2012
asked on Stack Overflow Oct 5, 2020 by user716255 • edited Oct 5, 2020 by user716255

2 Answers

0

I don't know why this occurred but the length of the field must be matched I mostly use Derived Column and set expression to change the length of string

answered on Stack Overflow Oct 6, 2020 by ali azizan
0

Found a work around. From Advanced Editor for Excel Source change the data type to Unicode string [DT_WSTR] Length = 2000

enter image description here

In the cell "Final BSC Description" of your destination excel file add dummy value of length greater than 255 characters. Then hide the entire row.

enter image description here

Got the idea from a post in stackExchange, option 2

answered on Stack Overflow Oct 8, 2020 by user716255

User contributions licensed under CC BY-SA 3.0