How do I add a header and footer to a ssis flat file destination?

How do I add a header row with the current date and a footer row with the row count to a flat file destination in SSIS?

Sample of output 1234567891234567~PE~400 9876543211234567~PE~52845

Needs to be H20120924 1234567891234567~PE~400 9876543211234567~PE~52845 T00000002

Can anyone help me with this?

asked Sep 24 '12 at 07:41 PM in Default

susanc
80 ● 2 ● 3 ● 4

(comments are locked)

6 answers:

I believe that this type of question has been asked in this forum previously.

http://ask.sqlservercentral.com/questions/49959/header-and-footer-ssis.html

This should give you a couple of ideas that might solve your issue.

Hope this helps!

answered Sep 24 '12 at 08:19 PM

JohnM
4.5k ● 1 ● 3 ● 7

(comments are locked)

I just figured it out! Thanks for all the assistance.

answered Sep 26 '12 at 12:42 PM

susanc
80 ● 2 ● 3 ● 4

Awesome!! Glad that you were able to get it figure out! Make sure to mark one of the answers as your solution so that others know that it's been solved. ;-)

Sep 26 '12 at 01:15 PM JohnM

(comments are locked)

Thanks, I got the header to work but I'm still having trouble with the footer. I am fairly new to writing script so I know I'm not doing something correct. Is there another way to do this without using a script? If not, could someone show me a "script for dummies" on how to do this?

answered Sep 25 '12 at 07:54 PM

susanc
80 ● 2 ● 3 ● 4

What is the footer doing? Any error messages? Also, what is your source data? A SQL table?

Sep 25 '12 at 07:59 PM JohnM

(comments are locked)

I'm not getting any errors, I am getting two output files; one with the header and detail information and the other with the footer information. I just need to merge these somehow.

On my Control Flow I have two Data Flow tasks. One returns the data I need from SQL tables which I have the header in that and the other data flow is a script to give me the row count. I have two flat file destinations, one has the detail and header results and the other has the footer count. I then have a script task on the control flow to append the output.

I have been researching this for a couple of days and got bits and pieces from different posts. If I need to include screen shots I will.

Thank you John for your assistance.

answered Sep 25 '12 at 08:29 PM

susanc
80 ● 2 ● 3 ● 4

Ok, so it sounds like the header is working as desired.

I don't think that you need to data flows. A single data flow would allow you to export the data from the table into a single file destination. On the control flow, you would then apply a script task to add the footer after the data flow.

A screen shot would help as well.

Sep 25 '12 at 08:56 PM JohnM

(comments are locked)

hi do not use the script task to append to files. Instead use a DFT to read the data in the trailer and write it to the existing file which has the header and detail.while doing this make sure you uncheck the check box of overwrite option.

answered Sep 26 '12 at 04:57 AM