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>.