Introduction
In this article you will learn how to edit SSIS Component Metadata and handle most common errors related to SSIS PowerPack Components. If you never heard about SSIS PowerPack then it’s addon pack to get additional 45+ SSIS components and Task (e.g. Connectivity to REST API, JSON, XML, Azure, AWS, Google, Salesforce etc).
SSIS Truncation Error (BufferException)
By default SSIS PowerPack components scan 300 rows to determine most accurate datatypes. However in many case you need to scan lot more rows to determine correct length and datatypes. If you go with default scan option to determine metadata then there is a chance that it may fail with the following error when you run SSIS package.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[JSON Source [2]] Error: System.Exception: BufferException: Truncation error with column [tickets[0].collaborator_ids] (to fix this error - see full message >> copy/paste this to text editor) Error: The value is too large to fit in the column data area of the buffer. >> Source: tickets[0].collaborator_ids, Actual Length: 13, Value: [18726645887] How to fix=> There are two ways to fix this error (see below steps). >>> Method 1 <<< - Goto dataflow designer and double click on the component which failed. - Once component UI is open, go to [Columns] tab and change column length and DataType so data is not truncated. - Optionally you can click [Refresh Columns] and select bigger length for all string columns by changing [Metadata Scan Mode] to more wider option (e.g. Guess2x to Guess4x). Check [Overwrite locked columns] option if needed so all columns are refreshed. - After you change length/datatype you can check [Lock] option so when metadata is refreshed for some reason your changes are preserved. >>> Method 2 <<< - To know which records causing failure you can also redirect error output by dragging red arrow from component to downstream component (When prompted set Truncate Action=Redirect). - If you simply dont care about such errors then Set Truncation action=Ignore in above step. . RowNumber=11 |
SSIS Runtime Error – BufferException / Truncation error
Possible Reasons
Such error can occur due to following reasons
- You used default metadata scanning option to detect metadata. Default option scans only 300 rows to guess datatype and length. If you have more than 300 rows then possible to have data which has longer length than found in first 300 rows.
- Underlying data is changed after you configured the component so now you have bigger length for one or more string columns
Possible Fix
To fix truncation related errors you can use any of the following approach.
Method-1: Change default scan options and re-scan
Perform following steps to scan records with custom options.
- Edit component and goto columns tab
- On the columns tab change default scan row count to larger number this will cover bigger scan range (Default is 300).
- Click [Refresh Columns] button to scan again.
- When prompted change Auto mode to Guess4x (i.e. Set length to 4 times bigger than maximum detected length).
- Also check Set lock flag so any future scan wont override your changes. If you want to wipeout existing metadata and start again then check [Overwrite metdadata] option too
- Click OK to start scanning. Depending on how many scan rows selected it may take several seconds.
- Once scan is Done you will see Grid refresh with new column length and datatype
SSIS PowerPack – Advanced Metadata Scan Options
Method-2: Fix metadata for the column(s) which are causing runtime error
Another approach to fix metadata related errors you get at runtime is only fix those columns which caused the issue. Carefully read the error message to find which column is causing BufferException (sometimes you have to copy full message into notepad to see it).
Once you know the column name which caused the error, you have two options.
1. Remove that column from output : Uncheck from Left side list
— OR —
2. Increase column length : For this you can go to Columns tab as below screenshot and fix the length (Increase by 3-4 times or more if you expect longer string). If you need more than 4000 chars long string then you have to DT_NTEXT ( or DT_TEXT). After fixing column length (or datatype) click OK and run package again. Keep doing this until you fix all errors.
SSIS PowerPack – Column Metadata Editor (i.e. Change DataType, Column Length)
Method-3: Redirect bad rows to error output OR Ignore bad rows
If you want to detect records which are causing errors then you can redirect bad rows to error output using following way.
Redirecting bad rows to error output
To know which records causing failure you can redirect error output by dragging red arrow from component to downstream component (When prompted set Error and Truncation options to [Redirect row]).
SSIS Component Error Handling – Redirect bad rows to error output (Attach to destination)
SSIS Component Error Output – Add data viewer to see bad records and error message
Ignore bad records
If you simply don’t care about such errors and want to ignore them rather redirect then Set Error and Truncation options to [Ignore failure] in above step.