?
Solved

SSIS package failing

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.

621 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