February 11, 2016 at 12:03 am
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
February 11, 2016 at 6:59 am
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.
February 11, 2016 at 8:37 am
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
February 11, 2016 at 11:15 am
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.
February 11, 2016 at 11:27 am
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.
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?
February 11, 2016 at 1:08 pm
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.
February 13, 2016 at 4:12 am
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);February 17, 2016 at 4:48 pm
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
February 17, 2016 at 11:52 pm
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
March 8, 2016 at 10:05 am
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
March 8, 2016 at 4:46 pm
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