Add Footer to the Export File RRS feed

  • 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?


    Wednesday, September 28, 2011 2:41 PM


All replies

    • Proposed as answer by Eileen Zhao Friday, September 30, 2011 9:08 AM
    Wednesday, September 28, 2011 6:19 PM
  • 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     
    Thursday, September 29, 2011 4:10 AM
  • 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.
    Thursday, September 29, 2011 1:29 PM
  • 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
    • Edited by Randy Paulo Thursday, September 29, 2011 1:49 PM
    Thursday, September 29, 2011 1:43 PM
  • 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.

    • Marked as answer by Eileen Zhao Thursday, October 6, 2011 6:15 AM
    Friday, September 30, 2011 9:13 AM