Discard Rows in a flat file

Say I have a flat file with the form

FHRxxxxxxxxxxxxxxxxxxxxx
SHR1xxxxxxxxxxxxxxxxxxxx
DataRow
DataRow
STR1xxxxxxxxxxxxxxxxxxxx
SHR2xxxxxxxxxxxxxxxxxxxx
DataRow
DataRow
DataRow
STR2xxxxxxxxxxxxxxxxxxxx
FTRxxxxxxxxxxxxxxxxxxxxx

Where FHR=File Header, SHR=Section Header, STR=Section Trailer & FTR=File Trailer

How can I discard all the header and trailer rows?

I have the manager defined as ragged right and have defined the fixed length columns.

I know I can tell the flat file connection manager to discard n header records. I can also tell it to redirect rows which are truncated to "nowhere" but some headers & trailers still get through (they're longer than data rows)

Is there any other way I can discard these rows? Something like "if this rows starts with SHR/STR/etc. ignore it"?

Ta.

Greg.

Greg,

You can use a conditional split transform to filter out these rows but this is AFTER they have been loaded into the pipeline. if you want to discard them BEFORE they get into the pipeline then you're probably into script source component or custom source adapter territory.

Donald Farmer has a fantastic example of how to load "irregular" files such as these using the script component in his book - you can find it on Amazon.

-Jamie

|||

Hey Greg,

the easiest way is the to use the conditional split component.

Your condition looks like this example:

SUBSTRING(Input,1,3) == "SHR"

For more power take a look at the scripting book of Donald Farmer.

I like the scripting component!!!

Loom

|||Thanks guys. Great stuff

Greg.

Disabling the flat file destination at run-time?

I noticed that The flat file destination that I am using, creates a blank file everytime even if there are no records processed by the data flow.

I just wanted to know if there is any way to stop this. I dont want to see a chunk of blank files on the disk. (I create the files with a timestamp in the file name so every run creates a new file.)

So I want to implement a logic to stop the creation of these blank files in case there are no records to write into it.

Any suggestion would be of great help.

Regards

Saurabh

One option is to use an Execute SQL in the control flow before the data flow to check the record count, and use a condition on a precedence constraint to the data flow to prevent it from running at all.

Another option is to use a Row Count component in the data flow to capture the number of rows written to the flat file. Set a File System task to delete the file after the data flow, with a precedence constraint that uses the row count to determine whether the File System task should execute.

|||

well the first option is not suitable for me because I cannot determine the number of records to be written beforehand.

second option looks good. I'll do that but again that will be having to do one extra task. if at all there is any way to stop the file creation then that will be great. (I am playing around with a script component with no luck)

Thanks for your response John!

Please get back if you find out any other way...

|||

jwelch wrote:

One option is to use an Execute SQL in the control flow before the data flow to check the record count, and use a condition on a precedence constraint to the data flow to prevent it from running at all.

Another option is to use a Row Count component in the data flow to capture the number of rows written to the flat file. Set a File System task to delete the file after the data flow, with a precedence constraint that uses the row count to determine whether the File System task should execute.

Or to adapt a theme, push the data into a raw file or recordset (http://blogs.conchango.com/jamiethomson/archive/2006/06/28/SSIS_3A00_-Comparing-performance-of-a-raw-file-against-a-recordset-destination.aspx) and then use a conditional precedence constraint (as suggested by John) to decide whether to push the data into your flat file or not.

Plenty of ways anyway.

-Jamie

|||

Sounds good! though me too didn't like to push the intermediate data to the disk, the performance analysis shown by Jamie is really helpful in making a decision.

Thanks for the response. I shall go with this solution insead of adding more complications to my short and sweet package!

Regards

Saurabh