Hello Everyone,

I have an SSIS package which takes a SQL Query and generates a PIPE (|) delimited flat file. Simple enough. However, I also need to add a Trailer record at the bottom of the file (footer). To make this happen I could either do a UNION ALL on the same query script or what I've done is to create another query with the Trailer record and using the Merge component simply merged and appended the trailer record at the botton of the actual file. The issue, however, is that the trailer record is always including extra PIPES at the end of the trailer. According to business, this wouldn't load properly at our vendor side due to the extra pipes in the trailer record as it'll be seen as additional columns.

Is there anyway to get rid of those extra pipes at the end of the trailer? Please see example below.

Trailer record should look like this:

TRAILER|A|B|C|D|

As of now, the appended Trailer record looks like this (additional pipes for each column in the main query script):

TRAILER|A|B|C|D|||||||||||||||||

Thank you all in advance for your insight.

MirS

I would do this:

once the file is written (w/o the trailer row at this point in time), manipulate on it by using a Script Task (a normal FileSystem call to append to it) to add the needed trailer.

Hi Arthur,

I know its been long time to ask a question on this thread. I need you help.

I am generating 5 flat files(pipe delimited) using a single ssis package. The source is the SQL query and dividing the files based on conditional split. So there are 5 precendence constraints pointinf to 5 flat file destinations. 

I need the flat file as following.

ID|Fname|LName|City|State|Zip
1|John|David|Chicago|IL|12345
2|Roy|Ricky|Phoenix|AZ|23456
3|Raj|Vicky|Phoenix|AZ|23450
4|Harry|Ricky|Phoenix|AZ|23451
5|Tom|Ricky|Phoenix|AZ|23458
6|Roy|Scott|Phoenix|AZ|23459
TrailerCount 6

Thanks

Just saw your post.

Did you manage to craft the process up?

It is no different than what is in the link I pointed you out to. You just need 5 such appends. It can be even done with a Script Task and some .net C# code that simply appends a line to file.

Yes,

This can be done by doing a script task and a small C# script. Its like appending the count to the end of the file.