Format files give a lot of the flexibility of DTS Transformations – you can skip columns, reorder the columns and do most of what you want.

They can also be used in conjunction with Bulk Insert to get the speed efficiency from Bulk Insert.

But if possible – order the table and the source data in the same way and use a sensible delimiter which isn’t in the data – if the data is from a PC based system then try to use tab delimiters – if it’s sourced from a mainframe look at ‘%’ ‘#’ or ‘$’ since they tend not to be extensively used in mainframes.

If the source file is generated by COBOL then it’s likely to be fixed length – so use that instead to create your format files – saves you 1 byte per column – if you’ve got a lot of fields e.g. y/n indicators then you can halve the data by using fixed format. FTP’s will be faster