ExcelSourceFile (SSIS) - eliminating validating metadata

I need some assistance. I am using an excel file to import data to SQL tables. The problem I am running into is having to replace the metadata of the output columns with the metadata of the external columns. I do not want to do this manually everytime.

[The metadata of the following output columns does not match the metadata of the external columns with which the output columns are associated:

Output "Excel Source Output": "<Column1>",<Column2>"

Do you want to replace the metadata of the output columns wit the metadata of the external columns?]



The excel file that I import, changes periodically. For example, I have five columns where the data in one of the columns may be less than 255 characters and then another time greater than 255 characters. I have attempted to use the Advanced Editor for Excel Source and change the Input and Output Properties to say Ntext to allow for greater than 255. Unfortunately evertime the data changes and is less than 255 characters the Input columns will change and reflect the 255 character length or Unicode string [DT_WSTR]. How can I keep the Input and Output properties from changing? How can I keep from having to validate the metadata evertime. I have also tried to turn the validate metadata option to false and still does not work. Data conversion does not work. I receive the error: falied to retreive long data for columns "<column1>.
Please advise.

Hi there,

Just an idea. Have you considered using CSV files? This way your columns can be delimited and then I don't think you would get the column formats appearing to change, hence avoiding this error.

It should be nice and easy to export your excel data as CSV manually; and I think there are files out there to convert XLS files to CSV which you could put to work for you.

Good luck.

Unfortunately I cannot use a CVS file. There are formated text fields which contain commas. Any other suggestions?

Tried URL encoding the text fields? or using another delimiter than a comma? The delimiter I use in bulk inserts is a tilde.