After generating an SQL table using powershell with column names from the header of a delimited text file, I have successfully imported the tab-delimited file with some long fields into the SQL table using the SQL data import wizard by manually specifying under the advanced tab DT_TEXTSTREAM for the 2 incoming fields that needed it. Now I want to automate this import and am trying Bulk Insert. How to specify large incoming data fields for bulk Import without using a format file. I cannot use a format file because the delimited files I get sometimes have extra data fields in the middle of the file, or sometimes don't have some fields. (That's why I drop and create the table first using powershell commands) If not Bulk Import what else can I use?
Saving the SQL import Wizard settings as an SSIS package probably wont work for me as the order and number of fields varies.
