{"id":1219,"date":"2017-06-06T20:01:41","date_gmt":"2017-06-06T20:01:41","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=1219"},"modified":"2017-10-27T13:16:49","modified_gmt":"2017-10-27T13:16:49","slug":"handling-ssis-component-metadata-issues","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/handling-ssis-component-metadata-issues\/","title":{"rendered":"Handling SSIS Component Metadata Issues"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>In this article you will learn how to edit SSIS Component Metadata and handle most common errors related to SSIS PowerPack Components<\/a>. If you never heard about SSIS PowerPack then it’s addon pack to get additional 45+ \u00a0SSIS components and Task (e.g. Connectivity to REST API, JSON, XML, Azure, AWS, Google, Salesforce etc).<\/p>\n

 <\/p>\n

SSIS Truncation Error (BufferException)<\/h2>\n

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\u00a0with the following error when you run SSIS package.<\/p>\n

[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)\r\nError: 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]\r\nHow to fix=> There are two ways to fix this error (see below steps).\r\n>>> Method 1 <<<\r\n- Goto dataflow designer and double click on the component which failed.\r\n- Once component UI is open, go to [Columns] tab and change column length and DataType so data is not truncated. \r\n- 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.\r\n- After you change length\/datatype you can check [Lock] option so when metadata is refreshed for some reason your changes are preserved. \r\n>>> Method 2 <<<\r\n- 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).\r\n- If you simply dont care about such errors then Set Truncation action=Ignore in above step.\r\n. RowNumber=11\r\n<\/pre>\n
\"SSIS<\/a>
SSIS Runtime Error – BufferException \/ Truncation error<\/p><\/div>\n
Possible Reasons<\/h3>\n
Such error can occur due to following reasons<\/p>\n