I am getting CSV files with hundreds of columns many of which have irregular characters in the column names, such as |, " ", -, /, etc. I've created table by manually importing the file, which renamed those columns in the table nicely by removing the character and replacing some of them with underscore. When I try to build a data flow to import the file into the existing table I have to manually map all those hundreds of columns as the names do not exactly match. If I create a new table the junk symbols go into the column names. Is there a way to tell the SSIS designer to map the columns based on the order?
Also my table has nvarchar columns and the connection manager considers the source files to be varchar, so I have to change every column in the connection manager to Unicode string. Is there a way to do it for all columns at once?