ZappySys Blog Logo
Toll Free: +1-800-444-5602 | Live Chat








  • Home
  • Products
    • SSIS PowerPack
    • ODBC PowerPack
    • ZappyShell
  • Blog
  • API Integration
    • API Connectors / Apps
    • Articles
  • FREE SSIS Tasks
  • About Us
    • Our Clients
    • Our Partners
    • Reviews and Ratings
  • Support
    • Contact Support
    • Product Online Help
    • Privacy Policy
Home » SSIS PowerPack » SSIS Components » SSIS JSON Source (File/REST) » Handling SSIS Component Metadata Issues

Handling SSIS Component Metadata Issues

Posted on June 6, 2017October 27, 2017 by ZappySys

Contents

  • 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 Share this:
    • 2.4 Like this:
    • 2.5 Related

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.

 

 

Share this:

  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • More
  • Click to print (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to share on WhatsApp (Opens in new window)

Like this:

Like Loading...

Related

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

  • How to download images from a web page using SSIS
  • How to save a list of files into a table using SSIS.
  • How to get all URLs from emails from Outlook
  • How to add row numbers in SSIS data flow
  • Connect to Infor Compass using JDBC Driver in ODBC Apps (e.g. Power BI, Excel, Informatica, SQL Server)

Categories

  • Announcements (33)
  • Cloud Computing (13)
    • AWS (Amazon Web Services) (12)
      • Redshift (5)
      • S3 (Simple Storage Service) (7)
  • Google API (12)
  • ODBC PowerPack (59)
    • 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 (49)
      • Amazon S3 CSV Driver (2)
      • Amazon S3 JSON Driver (2)
      • Amazon S3 XML Driver (2)
      • 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 (33)
      • 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 (36)
  • REST API Integration (66)
  • SSIS PowerPack (204)
    • SSIS Components (143)
      • 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 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) (63)
      • 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 (6)
      • 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 (13)
  • Tools (3)
  • Uncategorized (7)

Tags

access amazon api API Integration aws azure CSV Destination excel export fiddler google google api json json source MongoDB oauth oauth2 odata odbc pagination paging power bi redshift regex rest rest api s3 salesforce sftp soap source SQL sql server ssis ssis json source SSIS PowerPack ssis rest api task ssis xml source storage Task upsert write xml ZappySys

Archives

  • March 2023
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • April 2021
  • December 2020
  • September 2020
  • August 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • November 2019
  • October 2019
  • September 2019
  • August 2019
  • July 2019
  • June 2019
  • May 2019
  • April 2019
  • March 2019
  • February 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • June 2018
  • May 2018
  • April 2018
  • March 2018
  • February 2018
  • January 2018
  • December 2017
  • November 2017
  • October 2017
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • January 2016
  • December 2015
  • November 2015
  • October 2015
  • September 2015
  • August 2015
  • Facebook
  • Google+
  • Linkedin
  • Pinterest
  • Twitter
  • YouTube
%d bloggers like this: