Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SSIS package failing

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

972 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