I just recently had to load data in from a flat file, and had to ensure the order (well, I actually had to be able to get the first row with the column headers).

Solution:

BCP into a temp table with an identity column, using a format file to skip the identity column.

Example:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

CREATE TABLE #temp (RowData varchar(8000), RowID INT IDENTITY PRIMARY KEY CLUSTERED)

-- load the data file

PRINT 'BULK INSERT'

BULK INSERT #temp

FROM 'c:\<mypath>\<MyCSVFile>.csv'

WITH (FORMATFILE='C:\<mypath>\<MyXMLFormatFile.xml')

--Format file:

<?xml version="1.0"?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="8000"/>

</RECORD>

<ROW>

<COLUMN SOURCE="1" NAME="RowData" xsi:type="SQLVARYCHAR"/>

</ROW>

</BCPFORMAT>

Hope this helps