sql server dts: SSIS: Footer rows to skip -- sql server dts

SSIS: Footer rows to skip

Dima S
2/14/2006 12:41:52 PM

sql server dts: SQL Server 2005 - SSIS

OK. We know there is Header rows to skip options and it works great.

I've got the file that has a "footer". Here is an example:

..
PSC
filename=table1
records=0000000000525
ldbname=db1
timestamp=2006/02/07-16:25:00
numformat=44,46
dateformat=mdy-1910
map=NO-MAP
cpstream=ISO8859-1
..
0000260611

It's ALWAYS last 12 rows.

Is there a way to split at this point and put the 12 rows in a different
location? The task is twofold - I don't need these control rows in my data
and I need value of "records" to verify loaded number of rows.

UPDATED: After some testing I found out that the Flat File source does not
see that footer at all. This is good and bad - I do want to load this metedat
into some other tables.

Re: SSIS: Footer rows to skip

Allan Mitchell
2/19/2006 11:41:53 AM

Hello Dima,

Ok so you know that the footer begins with a certain sequence to indicate
the footer?

As you have discovered the Flat File Source adapter will not even recognise
the footer rows because they do not conform to its metadata. One way of
getting around this is to make the source adapter think there is only one
column in each row. I do this by giving the column delimiter a "known to
not be in the data" value.

Here is one way of doing what you want.

http://wiki.sqlis.com/default.aspx/SQLISWiki/FilteringFooterRows.html

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

[quoted text, click to view]