Add header and footer to csv output
I need to add a header row and footer row to my csv output - the data comes from MS SQL procedures. Where is the best place to do this? THe footer also needs to contain a count of records, if i add the header/footer using the SPs this is easy, but how would i do this in SSIS if the add the header and footer that way? cheers
November 8th, 2011 7:00am

take a look at these links: http://agilebi.com/jwelch/2008/02/08/adding-headers-and-footers-to-flat-files/ http://www.bidn.com/blogs/KeithHyer/bidn-blog/1990/adding-a-header-or-footer-row-to-a-fixed-width-file-using-ssishttp://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 8th, 2011 7:03am

Thanks - i've taken a look at those and thought i'd come up with a solution! However, I haven't! I added a global value for rowcount which the dataflow sets and my script uses to add to the footer of my file but this doen't work as the dataflow task is in a for each loop which loops through several stored procedures and the script which adds the header/footer is outside this loop. Here is the code I use: public void Main() { StringBuilder fileContents = new StringBuilder(); string finalFile = Dts.Variables["sFileName"].Value.ToString() + ".csv"; fileContents.AppendLine( "54,H,U,,"); fileContents.Append(File.ReadAllText(Dts.Variables["sFileLocation"].Value.ToString() + "goodrecords\\" + finalFile)); fileContents.AppendLine("54,T," + Dts.Variables["sRowCount"].Value.ToString()); File.WriteAllText(Dts.Variables["sFileLocation"].Value.ToString() + "goodrecords\\" + finalFile, fileContents.ToString()); Dts.TaskResult = ( int)ScriptResults.Success; } Any idea what i can do instead? Is there a way I can open the file and count the rows in my script? cheers
November 8th, 2011 12:19pm

Why not to place this script to append the footer on file completion then? Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
November 8th, 2011 2:05pm

Either put the script in the foreach loop as suggested by Arthur. Else put another script in the foreach loop which adds up the variable values after each run to another variable. Now use this variable outside the loop to get the cumulative row count.My Blog | Ask Me
November 8th, 2011 4:58pm

Why not to place this script to append the footer on file completion then? Not sure how I would do this! In my Data flow I have a script component which does various data validation and send each row to either a good or bad destination csv. As this script runs for every row and the row count comes after it (so i only count the good records) where would i add my script to add a header and a footer - there is no green 'out' arrow from the desitnation csv component? cheers
Free Windows Admin Tool Kit Click here and download it now
November 9th, 2011 5:36am

You can add a row count (shape) before the destination and assign it to a variable. Use the variable when constructing the footer. Randy Aldrich Paulo MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog
November 9th, 2011 5:41am

You can add a row count (shape) before the destination and assign it to a variable. Use the variable when constructing the footer. Randy Aldrich Paulo MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog I have done the first part (added a row count and assigned to a variable) but i thought the destination received the data row by row - therefore the footer code would fire for every row? Or is it the case that the pipeline loops through all rows before it finally pushes the final complete dataset to the destination?
Free Windows Admin Tool Kit Click here and download it now
November 9th, 2011 11:53am

as my understanding from above; you have a foreach loop container and inside it you have a data flow which runs a stored procedure dynamically and store its result into flat file. am I right? if yes, do you want to create a flat file for each iteration in foreach? or you want to create a single flat file for all iterations? and do you want the record count for each iteration or sum of all iteration records count? what values you want to be in header and what in footer? for an overall solution; if you want to create a file for each iteration, you can add a script task inside foreach loop after data flow and append any content you want to the flat file. if you want to create a single file for all iterations, you can add a script task outside the foreach and after it and append content to the file. for more detailed help, I need to know answers to my questions above.http://www.rad.pasfu.com
November 9th, 2011 4:10pm

as my understanding from above; you have a foreach loop container and inside it you have a data flow which runs a stored procedure dynamically and store its result into flat file. am I right? if yes, do you want to create a flat file for each iteration in foreach? or you want to create a single flat file for all iterations? and do you want the record count for each iteration or sum of all iteration records count? what values you want to be in header and what in footer? for an overall solution; if you want to create a file for each iteration, you can add a script task inside foreach loop after data flow and append any content you want to the flat file. if you want to create a single file for all iterations, you can add a script task outside the foreach and after it and append content to the file. for more detailed help, I need to know answers to my questions above.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 9th, 2011 11:50pm

thanks all for your help - after a lot of playing around, it was actually quite simple in the end!
November 14th, 2011 8:37am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics