SSIS: Case sensitivity may expose issues with change deployment

  • 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

    Follow me on
    Twitter: @sqltwins

  • 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.

  • 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

  • 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.

  • 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?

  • 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.

  • 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]

  • 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

  • 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

    Follow me on
    Twitter: @sqltwins

  • 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

  • Enlightening article, thank you.

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

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