Add Footer to the Export File

Question

Hi, Lets say i have a Table called Persons(Id, FirstName, LastName) and i have a One more table called footers(RecordCount,RecordType)

RecordCount column is having total number of records from Persons table. I am loading Persons table using a stored proc.

I have an SSIS package that moves data from Persons table to a text file.

So how do i add RecordCount and RecordType from Footers table to the bottom of Export file?

For Example: Lets say i have the following data in my export file.

Id      FirstName         LastName

1      Bradd                   Pitt

2      Anjelina                Jolie

3      Justin                    Timberlake

4      Mila                       Kunis

RecordCount = 4

RecordType = Hollywood

So you can see the 4 records are from Persons table and RecordCount and RecordType from Footers table.

Does anybody have any idea?

Thanks

Answer 1

This may help:

http://www.bidn.com/blogs/KeithHyer/bidn-blog/1990/adding-a-header-or-footer-row-to-a-fixed-width-file-using-ssis

---

Thanks http://dwhanalytics.wordpress.com/

Answer 2

You can use a Row Count transform to get the number of rows in the Data flow task and save it to a variable. I am ot sure how do you decide the Type. Once you have that inforamtion you can move to the control flow and use a Script Task after the data flow to insert the last 2 rows(footers) by using the count variable and setting the type as you want based on your logic. 

---

My Blog    |      Ask Me     

Answer 3

Thanks for your responce Sudeep. Do you know any article on that? Also is it possible if i use a conditional split transformation task. I know output from Conditional split would go to one destination so i was thinking if i could put Footer data as one record like RecordCount=4,RecordType=Hollywood. i dont know if we can do that but just guessing.

Answer 4

Add a scripting component and make it as the destination.  From the script you can access all the columns. From there you can format the text file anyway you want.

You can do                           OLE DB Source

                                                      |

                                             Conditional Split

                                              |                       |

                                         Format A               Format B

                                              |                        |

                               Scripting Component       Scripting Component

 

--------------------------------------------------------

My Blog: http://www.randypaulo.com

Answer 5

Hi NickJones01,

Please refer to the following article:

Adding Headers and Footers to Flat Files: http://agilebi.com/jwelch/2008/02/08/adding-headers-and-footers-to-flat-files/

Hope it helps.

Thanks,
Eileen