An argument can definitely be made for that in SSIS. For instance, to make a Lookup task case-insensitive in the Data Flow requires adding additional tasks to convert your input data to a common case. You also have to write a custom query for the Lookup which converts the database Lookup values to the same case. I've actually seen this adversely affect productivity, so I don't think the argument is without merit.

I think the degree to which it affects you depends largely on the tools that you're using at the time as well as developer expectations. For instance, most developers probably expect that if their database is set to a case-insensitive collation then the tools they use to access the database will follow suit. Even those of us who know better can still fall into the trap on occasion, when there's a disconnect between the collations and settings of the database and the tools that access it.