Solved

SSIS package failing

Posted on 2016-07-27
3
96 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Learn about cloud computing and its benefits for small business owners.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

932 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

6 Experts available now in Live!

Get 1:1 Help Now