John Dempsey (11/10/2009)

---

You can probably get away with just two Data Flow Tasks to generate the file. The first Data Flow Task would generate your Header and Detail records for the file. Then, the second Data Flow Task would be used to write your Footer record to the file. The following may be the steps you would take to develop this in your SSIS package:

1. Place 2 Data Flow Tasks on the Control Flow tab.

2. Use the first Data Flow Task to generate your Header and Detail records (rows)

a. Obtain the data from your data source

b. Transform your data as needed to satisfy the results for your Detail Rows

c. Set up your Flat File Destination in the layout for your Detail Rows. (Don't worry about Header yet)

d. Upon opening your Flat File Destination task you will notice a item for the "Header:". If you were to enter something in the box here it would be hard-coded as the header for you within your file. But, instead of it being hard-coded you can make it dynamic. You can create a SSIS variable to store your dynamically built header information, then assign it to the Header property of the Flat File Destination in the 1st Data Flow task.

3. Set dynamic Header SSIS variable to Header property of Flat File Destination in 1st Data Flow Task.

a. On the Control Flow Tab select the first Data Flow Task then go to the properties window for task

b. Expand [+] Expressions property and click the [...] button

c. In the Property Expressions Editor window click in the left column to bring up a list of properties available within the Data Flow Task for you Header and Detail Rows.

d. Choose the property [Flat File Destination].[Header] (if you haven't renamed it yet) setting the expression to the name of your SSIS variable name you created for your dynamic header.

4. Your dynamic header should now show up when you run the first data flow task with your detail rows.

5. Open up the second data flow and create a source for your footer, I used a script component as an input data source, mapping the source row to an SSIS variable that contained my Footer Row.

6. Create your Flat File Destination for you footer making sure that you set the Overwrite property = False. Your Flat File Destination will be the same file name as your header and detail file name from you first Data Flow Task, but the layout will be different in order to line up with the footer.

7. Upon running you SSIS, the package will run through executing your first Data Flow Task which writes the data from the Header property, then each detail row. Then, it will run the second Data Flow Task writing the footer row to the end of the same file used in your Header and Detail Data Flow Task.

Hope they helps get you started.

Thanks,

John