Bulk Insert a fixed width file

You can run BCP via command line from Sproc, but that may not be what you want. I was really thinking of debugging the process using BCP and then, using the knowledge from whatever problems you resolve, you build the BULK INSERT syntax.

For me I prefer bulk import from a command line because:

I can check that the file exists, and perform other tests on it (zero length files are not uncommon IME becuase of a Disk Full somewhere between Source Server and my SQL Server). BCP has the ability to trap errors to a file (both the command line output (using redirection) and also a specific command line parameter to cause BCP to log errors to a file.

We also tend to MOVE completed files to an Archive folder, and rename "the last 10 archive folders" so we have a short history of files - so when the user, eventually, screams because they found something wrong we can go back and see what actually happened.

Using BULK INSERT within SQL is much more like programming with both hands tied behind your back!