SSIS: Case sensitivity may expose issues with change deployment

  • Nakul Vachhrajani

    SSChampion

    Points: 10151

    Comments posted to this topic are about the item SSIS: Case sensitivity may expose issues with change deployment

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • natewest

    Old Hand

    Points: 307

    Another case where real database source control, rather than a custom deployment script, would have prevented this problem. The change script generated by Visual Studio database project publish would prevent the human error of an incorrect column case.

  • Russel Loski

    SSCertifiable

    Points: 6943

    If rather than using the Table source or even a command with "Select * from " you actually spelled out the column name "SELECT IsOrderAGift from " then the package will not have any problems when you rename the column to "IsOrderaGift."

    Russel Loski, MCSE Business Intelligence, Data Platform

  • kevin.a.larson

    SSC Enthusiast

    Points: 113

    Case-sensitivity is probably the worst concept that has ever been introduced into programming in general. There is no good reason why IsOrderAGift and IsOrderaGift should be considered different fields.

  • natewest

    Old Hand

    Points: 307

    kevin.a.larson (2/11/2016)


    Case-sensitivity is probably the worst concept that has ever been introduced into programming in general. There is no good reason why IsOrderAGift and IsOrderaGift should be considered different fields.

    See here for some excellent reasons why they should be.

    http://programmers.stackexchange.com/questions/9965/why-is-there-still-case-sensitivity-in-some-programming-languages

    And more for why they shouldn't be.

    http://www.hanselman.com/blog/CommentView,guid,a393244f-bd14-49d3-b76e-ac94753e00d8.aspx

    Moral: If you allow unicode in your column names, you need to consider the fact that not every language operates with exact 1-1 upper/lower case characters.

    E.G. beißen in all caps is BEISSEN. Should your language match those as being the same?

  • kevin.a.larson

    SSC Enthusiast

    Points: 113

    The "other language" argument is interesting, but if I was a German programmer, then, yes, I WOULD want the compiler to recognize that beißen and BEISSEN are the same.

  • mister.magoo

    SSC-Forever

    Points: 47068

    Thanks, for a well written, informational article.

    I'm not sure you have the research to provide weight to the theory that this is because .NET is case sensitive by default though. AFAIK, it is not case sensitive "by default", however XML is and SSIS packages are stored in XML, so...could be?

    Either way, it is a small point in an otherwise excellent post.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Tim Mitchell

    SSCoach

    Points: 15652

    Ah yes - VS_NEEDSNEWMETADATA. My favorite four-letter word 🙂

    SSIS is certainly fickle about metadata changes, and because it does a .NET comparison (rather than a database comparison, which is usually not case sensitive) to compare metadata, a change in case will often cause this issue. SSIS is picky about metadata for a reason, but this is one of those cases where the effects of the dogmatic metadata approach are painful.

    Good article. Thanks for pointing out this behavior.

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • Nakul Vachhrajani

    SSChampion

    Points: 10151

    Thank-you all very much for taking the time out to read my article and sharing your valuable views in the discussion!

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • TomThomson

    SSC Guru

    Points: 104763

    kevin.a.larson (2/11/2016)


    The "other language" argument is interesting, but if I was a German programmer, then, yes, I WOULD want the compiler to recognize that beißen and BEISSEN are the same.

    In that case you would presumably also want to recognise that "beissen" and "beißen" are the same, so it's not a case sensitivity versus case-insensitivity issue. Or discard the idea that equality is a transitive relationship. Would LEN cease to be a function, since it can deliver different values for the same argument?

    Despite those worries, I hate case-sensitive collation for most purposes, and I think I too would want scharfes S and ss to be treated as equal. Unless of course I was thinking of text specifically for use in Switzerland or Liechtenstein, in which case I would want ß to be treated as a mistake.

    And, frankly, the idea of case-sensitive variable names, column names, table names and so on strikes me as absolutely crazy; some char/nchar/varchar/nvarchar columns may need to be case sensitive, but I've found that case insensitive is generally more convenient; it's a pity that the declaration of variables doesn't permit case sensitivity or insensitivity to be specified, since it makes me specify the collation for each comparison if what I want isn't the database default.

    Tom

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Enlightening article, thank you.

  • Viewing 11 posts - 1 through 11 (of 11 total)

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