Solved

SSIS package failing

Posted on 2016-07-27
3
78 Views
Last Modified: 2016-08-08
Hi,

I have a very problematic SSIS package that uploads data from a CSV file to an SQL table.

Its working most of the time but is failing every so often and its obvious that its an issue with the CSV file data.

The errors are:

Message


Error: 2016-07-26 05:02:01.09     Code: 0xC02020A1     Source: Data Flow Task 1 Source - Serve_Me_incidents_csv [571]     Description: Data conversion failed. The data conversion for column "Column 4" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".  End Error  

Error: 2016-07-26 05:02:01.10     Code: 0xC020902A     Source: Data Flow Task 1 Source - Serve_Me_incidents_csv [571]     Description: The "Source - Serve_Me_incidents_csv.Outputs[Flat File Source Output].Columns[Column 4]" failed because truncation occurred, and the truncation row disposition on "Source - Serve_Me_incidents_csv.Outputs[Flat File Source Output].Columns[Column 4]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.  End Error


 Error: 2016-07-26 05:02:01.10     Code: 0xC0202092     Source: Data Flow Task 1 Source - Serve_Me_incidents_csv [571]     Description: An error occurred while processing file "C:\Extracts\ServeMe_CSV\incidents.csv" on data row 537.  End Error
 

So, Column 4 and line 537 are the things that stick out to me.

Line 537:
Line 537

Data Conversion:
Data Conversion
Destination Table:
Destination

My Investigations:

Line 537 column 4 is Russian text  - could this be the issue its failing?
The destination table is set to a varchar(max) and the dataconversion is set to string(847) so it cannot be anything to do with the length of the string.

Confused!  Hope someone can help.
0
Comment
Question by:SmashAndGrab
  • 2
3 Comments
 

Author Comment

by:SmashAndGrab
ID: 41731039
Data Conversion and code page.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41731595
<knee-jerk reactions>

>Line 537 column 4 is Russian text  - could this be the issue its failing?
Not likely.  If anything that would be a unicode to non-unicode conversion error, which means varchar should be nvarchar.  
But that would be a different error message.

Eyeball the rows around line 537 and make sure there's not a double-quote " in any of the strings, as that's the text column delimeter so that would throw off any data pump with columns well defined.   Might be referring to line 538 if there is a column header row.

Also double-check the mapping in the destination task to make sure the columns are mapped correctly.
1
 

Author Comment

by:SmashAndGrab
ID: 41734295
Thanks for the comments.

<Update>

I removed the row and re-run the job and it worked.  I then tried just changing the russian text to "BLAH BLAH BLAH" and it also worked so i'm assuming it must be something to do with this text.

Today, a new CSV file has been generated and it failed once again due to the same issue.

Error:
Actual Error

(When opened in Notepad ++...)
 Col 4
(When opened in Excel...)
Excel

I'm really no expert when it comes to page encoding types or such but ...


Could it be the encoding of the CSV file?
Could it be that I have the incorrect code page setup for the file in the SSIS package?    Is there a "catch all" code page?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now