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.
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.
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
Found a work around. From Advanced Editor for Excel Source change the data type to Unicode string [DT_WSTR] Length = 2000
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.
Got the idea from a post in stackExchange, option 2
User contributions licensed under CC BY-SA 3.0