Add header and footer to csv output

Question

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

Answer 1

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-ssis

---

http://www.rad.pasfu.com

Answer 2

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

Answer 3

Why not to place this script to append the footer on file completion then?

 

---

Arthur My Blog

Answer 4

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     

Answer 5

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

Answer 6

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

Answer 7

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?

Answer 8

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

Answer 9

thanks all for your help - after a lot of playing around, it was actually quite simple in the end!