I have a package that loops throgh a directory read each .xls file and imports the data into a SQL Server table. I am using a Script component in my Dataflow. Some of the files process ok but I am getting errors if the case of the column names do not match (ie. the column in the excel file is PhoneNumber and the database is phonenumber ) it seems to be case sensitive. I should not have to go into each spread sheet and check the case of the columns. How do I do this in my script so that it does not matter teh case of the column names and it will process the files without failing? this is the error in the execution results: [Excel Source[1] Error: Column "phonenumber" can not be found at the datasource but the the Excel file it is PhoneNumber

Strange. What are you doing in your script?Arthur My Blog

Did you checked with PhoneNumber and phonenumber ? and was it successful with one of them but failed with another?http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

I have a package that loops throgh a directory read each .xls file and imports the data into a SQL Server table. I am using a Script component in my Dataflow. Some of the files process ok but I am getting errors if the case of the column names do not match (ie. the column in the excel file is PhoneNumber and the database is phonenumber ) it seems to be case sensitive. I should not have to go into each spread sheet and check the case of the columns. How do I do this in my script so that it does not matter teh case of the column names and it will process the files without failing? this is the error in the execution results: [Excel Source[1] Error: Column "phonenumber" can not be found at the datasource but the the Excel file it is PhoneNumber before comparing/fetching the file name you could change the name in UPPER case. Let us TRY this | Mail me My Blog :: http://quest4gen.blogspot.com/

Unfortunately to the Excel driver and SSIS, column names are case sensitive. In order to work around this problem, you'll need to EXCLUDE the column name row from your source selection. You'll have to select only the range that contains your data. One way to do this is to edit the properties of your Excel Source component (press F4). I can't recall what the property is called, but it will currently contain the name of the sheet. Edit this to add an exclamation mark to the end (!), followed by an Excel range specification - like MySheetName!A2:F1200. You will also have to edit your connection manager to inform it that the data does NOT contain column names. Your data flow will now have generically named columns - such as F1, F2, F3. Talk to me now on

Thanks for all of your replies, Todd states that is is working as designed and yes the column names are case sensitive. What I was trying to accompolish is import spreadsheets with the same column names, there may be cases where they are not in the same order but if I can match on column names it does not matter. It appears my answer is to set requirements with my users that the spreadsheets column names must be consistant.