SSIS - Why No Failure/Error On Truncation?

  • autoexcrement

    SSCertifiable

    Points: 5880

    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

  • Site Owners

    SSC Guru

    Points: 80385

    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: 98299

    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.

  • autoexcrement

    SSCertifiable

    Points: 5880

    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: 243596

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • autoexcrement

    SSCertifiable

    Points: 5880

    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: 243596

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • autoexcrement

    SSCertifiable

    Points: 5880

    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: 243596

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • autoexcrement

    SSCertifiable

    Points: 5880

    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 10 (of 10 total)

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