x

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?
more ▼

asked Sep 24, 2012 at 07:41 PM in Default

susanc gravatar image

susanc
80 4 4 4

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

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!

more ▼

answered Sep 24, 2012 at 08:19 PM

JohnM gravatar image

JohnM
6.6k 1 3 7

(comments are locked)
10|1200 characters needed characters left
I just figured it out! Thanks for all the assistance.
more ▼

answered Sep 26, 2012 at 12:42 PM

susanc gravatar image

susanc
80 4 4 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, 2012 at 01:15 PM JohnM
(comments are locked)
10|1200 characters needed characters left
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?
more ▼

answered Sep 25, 2012 at 07:54 PM

susanc gravatar image

susanc
80 4 4 4

What is the footer doing? Any error messages? Also, what is your source data? A SQL table?
Sep 25, 2012 at 07:59 PM JohnM
(comments are locked)
10|1200 characters needed characters left

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

answered Sep 25, 2012 at 08:29 PM

susanc gravatar image

susanc
80 4 4 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, 2012 at 08:56 PM JohnM
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Sep 26, 2012 at 04:57 AM

aRookieBIdev gravatar image

aRookieBIdev
2.3k 47 55 61

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x927

asked: Sep 24, 2012 at 07:41 PM

Seen: 4252 times

Last Updated: Sep 26, 2012 at 01:15 PM