create a header for flat file destination using ssis

Question

I have a ssis package scheduled hourly and that package produces a flat file (text file) each time.

Is there a way to generate a header for each of those flat file generated?

I am thinking to use 'UNION' to merge two flat files (header & body), however I cannot control which file would go first.

Any thoughts?

Answer 1

Click on the connection in Connection Manager and set the "ColumnNamesInFirstDataRow" = True .

Answer 2

This might be simpler, depending on your preference:

1. Create a text file with your header
2. In an execute process task run this: COPY HeaderFile.TXT + InputFile.TXT OUTPUT.TXT to stick the files together

If you get an arrow character at the end of the file use this instead:

COPY /B HeaderFile.TXT + InputFile.TXT OUTPUT.TXT

(see an arrow → character getting appeded to files in a .bat script)

Answer 3

In my SSIS there is actually a property under Custom Properties named Header. Worked great for me.
* just discovered that you can also use an expression with the property [Flat File Destination].[Header] and set it to a variable on the Data Flow task.

Answer 4

I accomplished this by creating two separate data flow tasks. The first takes and totals data and adds in addition fields to make the first header record. The second task adds in the rows we their independent metadata. Trick is in the first flat file destination have it overwrite the previous file and in the second destination use the same file but uncheck the overwrite data. If the file name is dynamic you can set the file name as a global variable and initialize it on start and the time-stamped file name and then use the variable as the destination file name instead.

Answer 5

Absolutely. Using a script component is the easiest way I found to do it. I had played around with writing the data I wanted to a text file using a flat file destination, then using a script task to append a header and footer. You could do that if you like.

I found it was simpler to just do it in one step, i.e. route your data flow into a script task which writes the header in PreExecute, and all the data rows in ProcessInputRow, and optionally a footer in PostExecute.

Something like this;

TextWriter tw;

public override void PreExecute()
{
    base.PreExecute();
    tw = new StreamWriter(Variables.requestFname);
    tw.WriteLine("HEADER");
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    tw.WriteLine(Row.WhateverFieldsYouNeed);
}

public override void PostExecute()
{
    base.PostExecute();
    tw.WriteLine("FOOTER");
    tw.Close();
}

Answer 6

You can also use Properties of data flow.

Go to control flow page Select properties pan of Data Flow component Go to Expressions and select [Flat File Destination].[Header] Here you can Set the value for this property dynamically or not