SSIS Data Conversion Failed for Flat file to SQL table ?

Question

SSIS Data Conversion Failed for Flat file to SQL table ?

aloshya

Ten Centuries

Points: 1105
More actions
May 10, 2018 at 2:48 pm

#330807

Hello
I have around 40 flat files to move to my sql database
My destination table look like this
while importing from flat file to destination i am receivng the following error

my column 4 is a mix of characters,strings and Arabic text .
i tried
1 ) altered destination column to varbinary (max) but not successful
2 ) Column 4 converted to unicode_textstream_DTNTXT ,and failed ?
How to copy from flat files to destination successfuly ?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

It's a truncation error, not a conversion. Increase the size of the Length, it seems 50 is not large enough. Assuming it is mapping to ENTERVALUE, it's (about) 462 characters too short.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

aloshya Ten Centuries Points: 1105 More actions · Answer 2

Thom A - Thursday, May 10, 2018 2:56 PM
It's a truncation error, not a conversion. Increase the size of the Length, it seems 50 is not large enough. Assuming it is mapping to ENTERVALUE, it's (about) 462 characters too short.

Its returning same error for any length . i tried 512,524 and 600

Thom A SSC Guru Points: 99126 More actions · Answer 3

aloshya - Thursday, May 10, 2018 3:21 PM
Thom A - Thursday, May 10, 2018 2:56 PM
It's a truncation error, not a conversion. Increase the size of the Length, it seems 50 is not large enough. Assuming it is mapping to ENTERVALUE, it's (about) 462 characters too short.
Its returning same error for any length . i tried 512,524 and 600

And what is the longest value in Column 4?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

aloshya Ten Centuries Points: 1105 More actions · Answer 4

Thom A - Friday, May 11, 2018 1:51 AM
aloshya - Thursday, May 10, 2018 3:21 PM
Thom A - Thursday, May 10, 2018 2:56 PM
It's a truncation error, not a conversion. Increase the size of the Length, it seems 50 is not large enough. Assuming it is mapping to ENTERVALUE, it's (about) 462 characters too short.
Its returning same error for any length . i tried 512,524 and 600
And what is the longest value in Column 4?

Not more than this

Acknowledged alarm "98236DR-Communication Lost -Emergency GARestored/Ø§Ù„Ø³Ø§ØÙ„ÙŠØ© Ø§Ù„Ø£Ù…Ø±ÙŠÙƒÙŠØ© Ø£Ù†. Ø¬Ø¹Ù„ Ø¨Ù„ Ø¨ÙØ±Ø¶ Ø§Ø±ØªÙƒØ¨Ù‡Ø§ Ø§Ù„ØªÙ‚Ù„ÙŠØ¯ÙŠØ©. Ù…Ù…Ø§ Ù†ØªÙŠØ¬Ø© " AR882-MECHSQWSI

aloshya Ten Centuries Points: 1105 More actions · Answer 5

@ Eirikur Eiriksson
@ Phil Parkin
@ sgmunson
@ John Mitchell-245523

Thom A SSC Guru Points: 99126 More actions · Answer 6

aloshya - Monday, May 14, 2018 4:30 AM
@ Eirikur Eiriksson
@ Phil Parkin
@ sgmunson
@ John Mitchell-245523

Mentioning doesn't exist in SSC.

Back on topic, I assume that this is occurring straight at the source. If you set the source to not fail on truncation errors on that column, what does your data look like? Do you notice any truncated text?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Rick-153145 SSCrazy Points: 2823 More actions · Answer 7

Just a quick point, did you change the output value as well as the source value in advanced editor? If you have only set one, then it will still fail on the other with the same error.

sgmunson SSC Guru Points: 110639 More actions · Answer 8

Thom A - Monday, May 14, 2018 4:32 AM
aloshya - Monday, May 14, 2018 4:30 AM
@ Eirikur Eiriksson
@ Phil Parkin
@ sgmunson
@ John Mitchell-245523
Mentioning doesn't exist in SSC.
Back on topic, I assume that this is occurring straight at the source. If you set the source to not fail on truncation errors on that column, what does your data look like? Do you notice any truncated text?

You lucked out with me noticing this topic, as what Thom A indicates is true, "mentioning" doesn't do anything in this forum. Thom is also correct in suggesting you configure your source to not fail on a truncation error, and see what kind of result that produces. You'll also want to do the same on your destination, as Rick-153145 suggests. Once you test it and see what the results are, post back and we can go from there...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)