Adding Header and Footer to Flat File in SSIS

If you work with SSIS sooner or later you will run into issues of how to add header and/or footer to generated file. There are plenty questions about that process and little answers. Most suggestions use some kind of script component. For me that's not an answer, since it requires writing code in VB.Net which I won't touch with a 10 foot pole.
So I had to improvise. My requirement is to create flat file, with header that contains sequence number and date and footer that contains row count.

My approach: three Data Flows and some creative manipulation of the files.

First data flow extracts data, calculates and stores row count in the variable, then generates body of the file. For this file I'm using Flat File Connector that defines all the columns that go into it (let's call this file Body.txt).

Second Data Flow generates header and footer flat files. Sequence number comes from database table as well. The format of the header is something like "PREFIXYYMMDDNNNNNNN ...". After extracting sequence number from the table I used Derived Column transformation to create 4 columns: Prefix (expression set to be string "PREFIX"), Timestamp (expression set to be appropriately formatted date), SeqNumber (sequence number converted to string and zero padded on the left) and Trailer (whitespace replicated to fill in the rest of the line). The result was dumped in the flat file using Flat File Connector that has 4 fields matching the ones above (let's call it Header.txt).

Footer is a bit trickier, since there is no data source (the only changing field in there comes from variable) and I didn't want to use dummy file. I solved this problem by adding Multicast transformation right after extracting Sequence number. Now I got my one row and the rest is easy. Derived Column transform to create columns, dump to flat file using Flat File connector with fields (Footer.txt).

Now we need to combine this three files into one in the right order. None of the File System task seem to be up to the challenge, so I created third Data Flow. In it I open three files generated earlier, but using different Flat File Connectors - the one that has just one field, spanning whole line length. I guess my task is simplified here since my final file is fixed length. Now I have three sets of data that I can combine and dump into the final file. To combine data sets there is a Merge transformation, but unfortunately the final order is rather random. To avoid this problem I again used Derived Column transform to create ID column in each data set. Header is given ID 1, all the actual data records are set to have ID 2, and Footer got ID 3. The rest is simple: merge, sort by ID, dump into flat file using another Flat File Connector that has just one single field.

And here you go - after only couple of days I was able to generate a file in SSIS with header and footer. I wish I had other option...

Gogle Ad Sense going crazy

Install Java
Find Install Javas at Great Prices.
www.??????.com

Yes, I will install my Javas at Great Price, thank you.

Almost there

So we got new offshore support team. They seem to be better then the old team, who used to keep their own copy of source files while completely ignoring the repository. New folks know what Subversion is and how to check out the files. They even know how to check them back in. All the need now is little clue on how whole bug fixing life-cycle works, and I quote "So do we need to roll code to production and then check it in?". Errrrm..... NO
Maybe I'm being too optimistic, time will tell.

Idiots

Roughly translated quote from some Russian talk-show last night: "if we trust in Russian industries - we should enforce protectionism". WTF? Does this guy really think that Russian industries will suddenly start to flourish if they just close the borders? Or 70 years of "socialism" didn't teach him anything?

Think of the children, again

New "thinkofthechildren" law. I really don't like where it's all going.
If this law will be passed (and who really have the balls to oppose "thinkofthechildren" law) - everyone who runs WiFi router at home will be a criminal by default and not just some misdemeanor kind - sex offender kind. I'm sure no one will go and actively prosecute people for that, but if need be - look, suddenly you are aiding pedophiles, he must be a pedophile himself, burn him at the stake. Prosecutors have a very nasty habit of stretching the law to "bring people to justice". In this case they won't even have to stretch far.
This country seriously needs huge injection of common sense and responsibility.