Post reply

SSIS - Why No Failure/Error On Truncation?

  • 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

  • September 13, 2019 at 12:10 am

    #3679679

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

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

  • September 13, 2019 at 11:38 am

    #3679773

    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

  • September 13, 2019 at 3:02 pm

    #3679852

    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

  • September 13, 2019 at 5:09 pm

    #3679885

    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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • September 13, 2019 at 6:25 pm

    #3679901

    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

  • September 13, 2019 at 6:50 pm

    #3679913

    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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • September 13, 2019 at 11:34 pm

    #3680007

    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

  • September 14, 2019 at 12:31 am

    #3680010

    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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • September 14, 2019 at 12:35 am

    #3680011

    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

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

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