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...
