SSIS, Flat file connection header, footer with different column width

Question

I have a data flow task that gets the data from a proc, counts the rows and adds the rows to a flat file with some 20 columns each with different output column width,specified in the advanced tab of the flat file connection manager. The flat file destination object overwrites the file every time and the name of the file is created dynamically.

Now what I need to do is add a header and footer row to that existing flat file with only 5 columns, each with their own width. Values for the header and footer are not coming from the data set used in the above data flow task.

I think it will be its on flat file connection object with 5 columns in it. Some column values can be gotten from the variables.

How can I append a row of header and footer to the existing file coming from the data flow task. I am not sure how to go about doing that..

Answer 1

Since the header and footer rows use different data, you can use three Data Flow Tasks (one for the header, current output, and footer). On the Control Flow tab, use Precedence Constraints to link each DFT in the correct order. For the header and footer tasks, add the necessary source components and set the output to a Flat File Destination. Create a string variable that will contain the name of the output file that all the results will be written to and set this for the ConnectionString expression on all the Flat File Connection Managers that are used. This will ensure they all write to the same file. On the first (header) DFT, select the "Overwrite data in the file" option on the Flat File Destination to ensure that a new file will be created. This can also be done by using the Advanced Editor, going to the Component Properties pane, and setting Overwrite to true. On the second (current) and footer DFTs, set the overwrite option to false so that the data will only be appended to the file for these tasks.