Improve company productivity with a Business Account.Sign Up

x
?
Solved

SSIS package failing

Posted on 2016-07-27
3
Medium Priority
?
187 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 66

Accepted Solution

by:
Jim Horn earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
An ASP.NET Web Form User Control is not newly introduced in ASP.NET. In fact, it was an old technology yet still playing a role to generate web content, especially when we want to use it to have a better and easy way to control part of the web conte…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

606 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