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.
I really like your approach to the header/detail/footer issue. I'm new to SSIS so please forgive me I know this is an old post, however, how do I map the source row to a variable. If I make my flat file destination the same file as my detail my mapping is that of my detail.
My assumptions are that it is a source script component with one output and output column. How do I map to variable?
Thank you for any assistance with this.
It appears you have to questions, the different mappings for your connection manager and creating a source row from a variable. The answer follow starting with the mapping.
You will have two Data Flow Tasks and two Connection Managers. The first Data Flow Task will be for the Header & Detail rows using the Connection Manager with the Details layout. The other Data Flow Task will be for your Footer with a different Connection Manager object to the same file name, but with a differenct layout. You also need to make sure that the Footer's Data Flow Task Flat File Destination component property "Overwrite" is set to false. The reason for this is that we are pointing to the same file as the Details connection, but with a different layout and want to append to the file. If we didn't do this we would always overwrite the file with just the footer.
In order to create a source row from a variable following my steps, you would need to place a Script Component in the Footer Data Flow Task and select the "Source" radio button to indicate how it will be used. You will need to make sure you include your SSIS variable name in the Custom Properties section of the component in either "ReadOnlyVariables" or "ReadWriteVariables" depending on how you will use it. You will then script your row to equal the SSIS Variable you created for your Footer row. Please see Books Online for further info on Script Component tasks in the Data Flow and coding examples.
Hope that helps,