Off the top of my head, you could import all the rows including the Trailer row (but not the Header row) into a staging table that contains all the columns you need plus an extra column that is simply an auto-incrementing IDENTITY column with a seed value of 1 and an increment of 1 Then use an Execute SQL task to hold the maximum row number in the staging table - the SQL command would be 'SELECT MAX(RowNum( FROM <staging_tablename> (assuming that you've called your identity column RowNum)and the ResultSet property would be set to 'Single row'. You could then store the result set in your variable called, say, MaxRowNumber.

Next, create a Data Flow task containing an OLEDB Source component and an OLEDB destination compoment. Set the 'Data access mode' to 'SQL Command' and then write your query as:

SELECT * FROM staging_tablename WHERE RowNum < ?

Now click on the 'Parameters' button and select the User::@MaxRowNumber variable from the Variable drop-down list.

Finally, configure the OLEDB Destination component to write rows to your intended target table.

There may be other ways to accomplish your goal (the easiset of which would be to remove the offending trailer row from the file before it is loaded, but I'm assuming that you can't do that otherwise you'd already have tried! ;-)), but this is a starter for 10.

Regards

Lempster

For some reason the < symbol is not displaying correctly.