bilx10 (10/26/2008)
I know my data very well and also that ADO in excel doesn't handle columns with mixed data. A cell with numeric data will be taken as numeric and any data with alphanumeric characters ignored even if the destination is varchar datatype.
Hmmm, I don't think I've seen this, but I'm definitely going to watch for it now.
bilx10 (10/26/2008)
Of course if the excel files where properly created with each column formatted to the right datatype and data validated and verified there would be no problem with ADO (its an ADO problem not BIDS) but if you have been able to get the business units providing the excel files to do that please let us in on the secret of how thats done - most of the business users creating these sources don't have a clue what a datatype is and have no interest in learning. That includes their Management so there is no support for improvement.
I completely agree with you on that. Sigh 🙁 I think as DBAs we all try to impart the importance of following basic business rules to management, but I think as part of life, we're all disappointed when those rules are consistently broken with no consequences to anyone but us (and the users of the system who depend on timely and accurate data).
Cheers,
-m