DTS - Import Text file - varying column count

I receive a pipe delimited file without headers, and a header and trailer
row added as a check to each file. When I say header I don't mean column
headings, I mean a row of information that verifies the contents of the file
(example below)
The header info contains 5 columns, trailer info contains 7 columns, and the
normal data contains 28 columns.
The import succeeds when there is data (the header/trailer rows inserts NULL
into the remaining columns which is fine), however when there are no rows,
only the header and trailer rows are in the file. This fails as the columns
from 8 onwards are not there.
Firstly, can I make the DTS not "fail" if the full number of columns are
present? Currently I'm using "on completion" at the upload which continues
the DTS processing, but it fails at the end.
Second option - can I do a quick check to see if there are only 2 rows in
the file, and if so, maybe throw in a line of 27 pipes in to make the DTS
work, then delete the NULL row?
example file:
IMPORT_HEADER|HR|2006|04|18
datarows|etc|etc|28 columns in total
datarows|etc|etc|28 columns in total
datarows|etc|etc|28 columns in total
IMPORT_TRAILER|TR|2006|04|18|3|123.99
Thanks,Hi
You may find better advice in the DTS newsgroup
microsoft.public.sqlserver.dts , but you may be able to use an activeX
transformation to determine if you are reading a header row, data row or
footer assuming that the first column defines the row type.
Check out http://www.sqldts.com/default.aspx?279 and possibly
http://www.sqldts.com/default.aspx?266 and
http://www.sqldts.com/default.aspx?282. You should also be able to skip the
insertion of the header and trailer by returning DTSTransformStat_SkipInsert
or DTSTransformStat_NoMoreRows (if the row is the footer or the row is the
header and it says there are no rows within the file!) to make it cleaner
see the topic "DTSTransformStatus" in Books online for more.
John
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:OM91g.40851$Ph2.9628@.newsfe4-gui.ntli.net...
>I receive a pipe delimited file without headers, and a header and trailer
>row added as a check to each file. When I say header I don't mean column
>headings, I mean a row of information that verifies the contents of the
>file (example below)
> The header info contains 5 columns, trailer info contains 7 columns, and
> the normal data contains 28 columns.
> The import succeeds when there is data (the header/trailer rows inserts
> NULL into the remaining columns which is fine), however when there are no
> rows, only the header and trailer rows are in the file. This fails as the
> columns from 8 onwards are not there.
> Firstly, can I make the DTS not "fail" if the full number of columns are
> present? Currently I'm using "on completion" at the upload which continues
> the DTS processing, but it fails at the end.
> Second option - can I do a quick check to see if there are only 2 rows in
> the file, and if so, maybe throw in a line of 27 pipes in to make the DTS
> work, then delete the NULL row?
> example file:
> IMPORT_HEADER|HR|2006|04|18
> datarows|etc|etc|28 columns in total
> datarows|etc|etc|28 columns in total
> datarows|etc|etc|28 columns in total
> IMPORT_TRAILER|TR|2006|04|18|3|123.99
> Thanks,
>