Here’s one that’s got me stumped. I found one old thread from someone with the exact same problem, but no clues. The bulk statement works fine when run by itself. But when using EXEC, it will work for maybe 3-5 times, then give the error:
Cannot bulk load. Invalid destination table column number for source column 1 in the format file "D:\DMS_Import\_BAT_FMT_Files\CustOrds.fmt".
set @sql = 'BULK INSERT '+@BulkTableName+' FROM '+''''+ @FileToImport+''''+ ' WITH (FORMATFILE= '+''''+@FormatFile+''''+')' EXEC(@sql) The @sql value is: BULK INSERT BULK_CustOrds FROM 'D:\DMS_Import\CustOrds.txt' WITH (FORMATFILE= 'D:\DMS_Import\_BAT_FMT_Files\CustOrds.fmt')
I need to do this in a loop so I can import many files with different formats. Any ideas?