Skip to content
ZappySys Logo - SSIS, SQL Server, Big Data, Cloud Computing, NoSQL, Business Intelligence
ZappySys is a USA-based company with 10+ years in business and 3000+ customers in 90+ countries. Our mission is to provide user friendly, high performance drag-and-drop API Connectors, ODBC drivers, and no-code/low-code tools to automate data integration workflows and ensures maximum ROI.

Tel: +1-800-444-5602 | Live Chat

  • Home
  • Products
    • SSIS PowerPack
      • Product Overview
      • Compare Editions
      • Download FREE Trial
      • Documentation
      • Pricing
    • ODBC PowerPack
      • Product Overview
      • Compare Editions
      • Download FREE Trial
      • Documentation
      • Pricing
    • ZappyShell
    • ————————
    • Customer Download Area
  • Pricing
    • SSIS PowerPack
    • ODBC PowerPack
    • ZappyShell
  • API Integration
    • API Connectors / Apps
    • Articles
  • FREE SSIS Tasks
  • Blog
  • Community
  • Support
    • Contact Us
    • Product Documentation
    • Knowledge Base
    • FAQs
  • About Us
    • Our Customers
    • Our Partners
    • Resellers
    • Reviews & Testimonials
    • Privacy Policy
    • Terms & Conditions
  • Menu
Home » Handling SSIS Component Metadata Issues

Handling SSIS Component Metadata Issues

Updated on October 27, 2017 by ZappySys
Contents hide
1 Introduction
2 SSIS Truncation Error (BufferException)
2.1 Possible Reasons
2.2 Possible Fix
2.2.1 Method-1: Change default scan options and re-scan
2.2.2 Method-2: Fix metadata for the column(s) which are causing runtime error
2.2.3 Method-3: Redirect bad rows to error output OR Ignore bad rows
2.3 More from ZappySys Blog

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

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

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)

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 downstream destination)

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

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.

 

 

More from ZappySys Blog

  • Update MongoDB Array Items using SSIS
  • How to call Elasticsearch API using SSISHow to call Elasticsearch API using SSIS
  • Convert CURL to SSIS PowerPack / ODBC for API CallConvert CURL to SSIS PowerPack / ODBC for API Call
  • Call Semantics3 REST API in SSIS use OAuth1Call Semantics3 REST API in SSIS use OAuth1
Posted in SSIS JSON Source (File/REST), SSIS PowerPack, SSIS XML Source (File / SOAP) and tagged metadata, ssis, ssis json source, SSIS PowerPack.

Post navigation

← How to get data from…
SSIS Error Handling – Ignore… →
SSIS PowerPack - Collection of custom Tasks, Sources, Destination s and Transform
70+ high performance, drag and drop connectors/tasks for SSIS
Download Read More
ODBC PowerPack - Collection of ODBC Drivers for REST API, JSON, XML, SOAP, OData
ODBC Drivers for REST API, JSON, XML, SOAP, OData … Integrate inside Apps like Power BI, Tableau, SSRS, Excel, Informatica and more…
Download Read More

Recent Posts

  • Product release announcements have been moved to the Community Site
  • How to upload files on SharePoint Online using SSIS
  • How to download files from SharePoint Online using SSIS
  • How to download files from OneDrive using SSIS
  • How to upload files on OneDrive using SSIS

Categories

  • Announcements (44)
  • Cloud Computing (13)
    • AWS (Amazon Web Services) (12)
      • Redshift (5)
      • S3 (Simple Storage Service) (7)
  • Google API (14)
  • ODBC PowerPack (61)
    • ODBC App Integration (24)
      • ETL – Informatica (3)
      • ETL – Pentaho Kettle (1)
      • ETL – Talend (1)
      • Reporting – Google Sheet (1)
      • Reporting – Microsoft Access (4)
      • Reporting – Microsoft Excel (1)
      • Reporting – Microsoft Power BI (8)
      • Reporting – Microsoft SSRS (2)
      • Reporting – MicroStrategy (1)
      • Reporting – Qlik (1)
      • Reporting – Tableau (2)
    • ODBC Drivers (51)
      • Amazon S3 CSV Driver (2)
      • Amazon S3 JSON Driver (2)
      • Amazon S3 XML Driver (2)
      • API Driver (1)
      • Azure Blob CSV Driver (1)
      • Azure Blob JSON Driver (1)
      • Azure Blob XML Driver (1)
      • CSV File / REST API Driver (1)
      • JDBC Bridge Driver (1)
      • JSON File / REST API Driver (34)
      • Salesforce Driver (1)
      • XML File / SOAP API Driver (22)
    • ODBC Gateway (17)
    • ODBC Programming (14)
      • C# (CSharp) (2)
      • JAVA (1)
      • PHP (1)
      • PowerShell (1)
      • Python (2)
      • T-SQL (SQL Server) (9)
  • REST API (37)
  • REST API Integration (66)
  • SSIS PowerPack (211)
    • SSIS Components (148)
      • REST Connector (3)
      • SSIS Amazon DynamoDB Destination (1)
      • SSIS Amazon DynamoDB Src (1)
      • SSIS Amazon Redshift Destination (1)
      • SSIS Amazon Redshift Source (1)
      • SSIS Amazon S3 CSV Dest (1)
      • SSIS Amazon S3 CSV Source (2)
      • SSIS Amazon S3 JSON Source (2)
      • SSIS Amazon S3 XML Source (1)
      • SSIS Amazon SQS Destination (1)
      • SSIS Amazon SQS Source (1)
      • SSIS API Destination (1)
      • SSIS API Source (6)
      • SSIS Azure Blob CSV Destination (1)
      • SSIS Azure Blob CSV Source (1)
      • SSIS Azure Blob JSON Source (1)
      • SSIS Azure Blob XML Source (1)
      • SSIS Azure Queue Storage Destination (2)
      • SSIS Azure Queue Storage Source (2)
      • SSIS Azure Table Storage Destination (1)
      • SSIS Azure Table Storage Source (1)
      • SSIS Conditional Split Transform (1)
      • SSIS CSV File Destination (2)
      • SSIS CSV Generator Transform (2)
      • SSIS CSV Parser Transform (1)
      • SSIS CSV Source (6)
      • SSIS Dummy Data Source (1)
      • SSIS Dynamics CRM Destination (3)
      • SSIS Dynamics CRM Source (3)
      • SSIS Excel Destination (2)
      • SSIS Excel Source (4)
      • SSIS Google Analytics Source (3)
      • SSIS HTML Table Source (2)
      • SSIS JSON File Destination (2)
      • SSIS JSON Generator Transform (8)
      • SSIS JSON Parser Transform (5)
      • SSIS JSON Source (File/REST) (62)
      • SSIS Merge Join Transform (1)
      • SSIS MongoDB Destination (3)
      • SSIS MongoDB Source (6)
      • SSIS PostgreSQL Destination (1)
      • SSIS PostgreSQL Source (2)
      • SSIS Recordset Destination (1)
      • SSIS Salesforce Destination (3)
      • SSIS Salesforce Source (4)
      • SSIS Script Component (1)
      • SSIS Set Variable Transform (1)
      • SSIS SFTP CSV Source (1)
      • SSIS SFTP JSON Source (1)
      • SSIS SFTP XML Source (1)
      • SSIS Sort Transform (1)
      • SSIS Template Transform (12)
      • SSIS Trash Destination (3)
      • SSIS Upsert Destination (7)
      • SSIS WEB API Destination (13)
      • SSIS XML File Destination (1)
      • SSIS XML Generator Transform (4)
      • SSIS XML Parser Transform (3)
      • SSIS XML Source (File / SOAP) (20)
    • SSIS Connection Manager (41)
      • HTTP Connection (6)
      • SSIS Amazon S3 Connection (3)
      • SSIS Azure Blob Connection (4)
      • SSIS Dynamics CRM Connection (1)
      • SSIS Excel Connection (2)
      • SSIS MongoDB Connection (1)
      • SSIS OAuth Connection (14)
      • SSIS PostgreSql Connection (4)
      • SSIS Salesforce Connection (4)
      • SSIS SFTP / FTP Connection (2)
    • SSIS Tasks (87)
      • SSIS Advanced File System Task (10)
      • SSIS Amazon DynamoDB ExecuteSQL Task (1)
      • SSIS Amazon Redshift ExecuteSQL Task (1)
      • SSIS Amazon Storage Task (10)
      • SSIS Azure Blob Storage Task (9)
      • SSIS CSV Export Task (6)
      • SSIS Download File Task (1)
      • SSIS Excel Export Task (3)
      • SSIS ForEach Loop Task (1)
      • SSIS JSON Export Task (2)
      • SSIS JSON Parser Task (2)
      • SSIS Logging Task (7)
      • SSIS MongoDB ExecuteSQL (4)
      • SSIS PostgreSQL ExecuteSQL Task (1)
      • SSIS Regex Parser Task (4)
      • SSIS Report Generator (SSRS) (1)
      • SSIS REST API Task (36)
      • SSIS Salesforce API Task (1)
      • SSIS SFTP Task (6)
      • SSIS Timer Task (1)
      • SSIS XML Export Task (2)
      • SSIS Zip File Task (1)
  • SSIS Tips & How-Tos (15)
  • Tools (3)
  • Uncategorized (10)
  • ZappyShell (1)

Tags

access amazon api API Integration aws azure CSV Destination excel export fiddler google google api json json source MongoDB oauth oauth2 odata odbc office 365 pagination PostgreSQL power bi redshift regex Regular Expression rest rest api s3 salesforce soap source SQL sql server ssis ssis advanced file system task SSIS JSON Generator Transform ssis json source SSIS PowerPack ssis rest api task ssis xml source storage upsert xml

Sitemap

  • Home
  • Products
  • Purchase
  • Product Documentation
  • Contact us
  • Privacy Policy
  • Terms & Conditions
  • Support

Follow us

RSS ZappySys Community – Latest topics

  • SSIS Tips: How to Schedule Your Packages Effectively
  • SSIS Tip: Extracting JSON Data Stored in SQL Server Tables
  • SSIS Tutorial: Converting Excel to PDF using Adobe APIs
  • SSIS tips: How to import JSON files to MongoDB
  • SSIS Tip: How to import JSON files into SQL Server
  • How to set default AWS credentials in SSIS or ODBC application
  • How to Fix the “This App is Blocked” Error in Google OAuth
  • SSIS tips: How to delete old files easily from SFTP
  • How to copy files with specific extensions using SSIS
  • How to move old files and compress them using SSIS (archiving files)
All rights reserved ZappySys LLC.
A SiteOrigin Theme