SSIS - Why No Failure/Error On Truncation?

Question

Post reply

SSIS - Why No Failure/Error On Truncation?

autoexcrement

SSCertifiable

Points: 5956
More actions
September 11, 2019 at 11:15 pm

#3679333

I tried Googling this but most complaints seem to be that SSIS is giving people truncation errors when they don't think they should have them.
I'm having the opposite problem: my SSIS package is "successfully" loading a long string into a CHAR(6) column and SSIS/SQL Server (?) is automatically truncating it. It gives me a warning, but not an error on execution.
I would rather it fail and throw an error as data longer than 6 characters in the source file indicates a problem.
I don't even know if this is an SSIS issue or a SQL Server issue. I'd expect one of those "string or binary data would be truncated" errors on the SQL Server side. Thoughts?
"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

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

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

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Thom A SSC Guru Points: 99133 More actions · Answer 2

This is because your source and/or Destination don't have Ignore Failure set on Truncation. Open up your Source/Destination node, by double clicking it. Then go to the Error Output Pane. Select all the cells in the Truncation Column (you can't select the column itself unfortunately) and then select Fail Component and Apply at the bottom.

Thom~

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

autoexcrement SSCertifiable Points: 5956 More actions · Answer 3

Thanks so much for the reply, but I can't see that that's the problem. Maybe you can look at these screenshots and let me know what I may be missing?

Here's the source file, everything is set to Fail:

src1

Here is the destination (a sql server table):

dest1

Here's the advanced editor for the destination, so you can see the properties of the input column in question. You can see that it has 100 character length.

dest2

And here's the destination column, which is 6 character length:

dest3

Any thoughts? Thanks again so much for your time!

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

If you add a data viewer after the source component in the DF, you should be able to verify whether the data has already been truncated (and therefore whether SSIS is the culprit).

Next, please tell us of the datatype and length of PayeeId in both External Columns and Output Columns.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

autoexcrement SSCertifiable Points: 5956 More actions · Answer 5

Thanks, I tried that and saw that the data was NOT truncated on the SSIS side.

The source file is Excel so it's just a generic text field that SSIS considers a 100 length string. The field in question is called "Copy of Employee ID".

The destination table is CHAR(6). It's called PayeeID.

I think you can see both of those in my screenshots above, hopefully I screenshotted the correct items you wanted to see.

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Phil Parkin SSC Guru Points: 246985 More actions · Answer 6

OK, I might know what's happening.

Your screenshots are for the destination, not the source.

So your column comes in as string(100), goes through the SSIS pipeline unhindered and then out to the target table, at which point the truncation happens.

If you want to generate an error, open the Advanced Editor on your data flow source component, and select the 'Output Columns' node, under 'Input and Output Properties' and change the column width there.

Next time you run it, a truncation error should occur.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

autoexcrement SSCertifiable Points: 5956 More actions · Answer 7

So the only way to do what I want is for SSIS to notify me right off the bat if the source file has data longer than I'm expecting? It still seems weird that SQL Server is quietly truncating this data, like there must be a setting somewhere for that no?

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Phil Parkin SSC Guru Points: 246985 More actions · Answer 8

I agree, and no setting that I can think of right now. Just goes to emphasize how important it is to get your pipeline data types in order.

And surely, if you want it to fail, the earlier the better?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

autoexcrement SSCertifiable Points: 5956 More actions · Answer 9

Yeah no argument that that solves the problem. I'm just still kinda surprised by how this played out and that there's no setting on the SQL Server side. But good lesson learned I suppose.

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura